4

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.

ErikE
  • 48,881
  • 23
  • 151
  • 196
  • 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. – ErikE Oct 29 '15 at 23:23

2 Answers2

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
Andrew Savinykh
  • 25,351
  • 17
  • 103
  • 158
  • 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](http://stackoverflow.com/a/33425886/57611) 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 Oct 30 '15 at 00:35
  • @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>` instead of `Expression>` (which you are actually doing in the linked answer) then there is no problem to start with. – Andrew Savinykh Oct 30 '15 at 01:00
  • Yes, the `int` part was a misstep. I never meant to write that. – ErikE Oct 30 '15 at 01:03
  • And wait, the link I gave you doesn't use `int` it uses the object (I edited that some minutes ago). – ErikE Oct 30 '15 at 01:06
  • @ErikE Yep, that's what I was saying. That you are using the object and not int =) – Andrew Savinykh Oct 30 '15 at 01:07
  • I see. Let me think about it. It might be surmountable. – ErikE Oct 30 '15 at 01:08
  • @ErikE In the light of your edit I removed the parts of my answer that became completely irrelevant. – Andrew Savinykh Oct 30 '15 at 01:12
  • My apologies for the mistake. – ErikE Oct 30 '15 at 01:13
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.

Danny Varod
  • 17,324
  • 5
  • 69
  • 111
  • 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. – ErikE Oct 29 '15 at 23:44
  • Also, just in case you didn't see it, the other answer provided pretty much what I was looking for. – ErikE Oct 30 '15 at 16:17
  • @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. – Danny Varod Oct 31 '15 at 00:00
  • 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. – ErikE Oct 31 '15 at 02:53