5

Q1. How do I manually create a dead-simple entity framework model for a one-column table in my database, and query it?

The table looks like this:

CREATE TABLE dbo.MyTable (
    Value int NOT NULL CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED
);

And I have a POCO to map to it:

public class MyTable {
    public int Value { get; set; }
}

Q2. Then, how do I query MyTable with an Expression<Func<MyTable, bool>> lambda that will decide which rows to return and will get projected into the SQL?

I am relatively new to EF, but not to C# or software development. I'm asking this question because right now I just want to do a quick proof of concept of something in LINQPad, without using the EF Entity Data Model Wizard so it's easy to whip out code like this in the future.

Improve this question
1
  • Oh my. I just buckled down and added EF to a VS project, and added an Ado.Net Entity Data Model from database, and had it use my one table to create a model. There are hundreds of lines of code added!!! Sheesh. Commented Oct 29, 2015 at 23:23

2 Answers 2

Reset to default
6

All you need is in the code below, ready to be pasted to LinqPad

class MyTable
{
    public int Value { get; set; }
}

class MyTableConfiguration : EntityTypeConfiguration<MyTable>
{
    public MyTableConfiguration()
    {
        ToTable("dbo.MyTable");
        HasKey(x => x.Value);
        Property(x => x.Value).HasColumnName("Value").IsRequired();
    }
}

class MyDbContext : DbContext
{
    public IDbSet<MyTable> MyTableSet { get; set; }

    public MyDbContext(string connectionString) : base(connectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Configurations.Add(new MyTableConfiguration());
    }
}

void Main()
{
    MyDbContext context = new MyDbContext("Data Source=(local);Initial Catalog=SO33426289;Integrated Security=True;");
    Expression<Func<MyTable, bool>> expr = x => x.Value == 42;
    context.MyTableSet.Where(expr).Dump();
}

You need to make sure to reference EntityFramework NuGet package and System.ComponentModel.Annotations.dll. Here are the namespaces that I used:

System.ComponentModel.DataAnnotations.Schema
System.Data.Entity
System.Data.Entity.ModelConfiguration
System.Data.Entity.ModelConfiguration.Configuration
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you! This is helpful. I got my proof of concept working in Visual Studio, but will play with this in LinqPad. I got partway down the path you laid out for me, but not far enough (the code ran but froze during the querying). Please see this question answer I wrote today for why working with an Expression was important in this case, where I seemingly solve most of the problems that the article you linked presents.
@ErikE, well, his is a slightly different problem. The article deals with inability of EF to translate compiled queries and queries that internally use other expressions via EF unsupported methods such as Invoke. Going back to your question, if you can use Expression<Func<MyTable, bool>> instead of Expression<Func<int, bool>> (which you are actually doing in the linked answer) then there is no problem to start with.
Yes, the int part was a misstep. I never meant to write that.
And wait, the link I gave you doesn't use int it uses the object (I edited that some minutes ago).
@ErikE Yep, that's what I was saying. That you are using the object and not int =)
|
0
  1. Either use EF's code first (e.g. data annonations) to define mappings and create a context class to access the entity set through or use an EDMX (model first or database first) for creating the models and the mappings and have the model and context generated for you. Search for any getting started with Entity Framework guide online.

E.g. Code First (Search for Create the Data Model in this page) or for EDMX (Search for Creating the Entity Framework Data Model in this page).

  1. Like so:

using (var context = new YourContext()) { var filteredEntities = context.YourEntities.Where(expression).ToList(); }

However, your table will need a PK (primary key) for this to work.

Improve this answer

4 Comments

For what it's worth, I did search for a while, but I wasn't grasping even the basic concepts yet of what a DbContext or Dbset are. Once I added EF to a VS project and poked around, I finally did start getting deeper and finding terms that helped me do further searching.
Also, just in case you didn't see it, the other answer provided pretty much what I was looking for.
@ErikE The other answer was added after mine and in my opinion is too much of a spoon feeding. The two links I provided (especially with the exact section name to look at) give a better explanation on how to solve the problem in various ways - i.e. not only code first, which in my opinion may more complicated to start with for less the experienced with ORMs.
I really have no desire to deeply understand EF right now. I just wanted a dead simple working example. Call it spoon feeding if you like, but that's what I asked for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.