How to generate an entity model from a query w/ EntityFrameworkCore

Question

I'm trying to connect an ASP.NET Core web application to an existing, pretty complex database, in read-only mode.

The database is much to complex to map its layout to EFC directly, I just access data from it via a set of queries. Those queries are well-defined, so I can define objects that match their results in advance without problem.

However, I can't seem to find out how to define the entity model for the database context for this. I can't, of course, set a TableAttribute on the model class - because the model doesn't reflect a table, but simply a query result. Just adding ColumnAttributes to the model's properties doesn't seem to do the trick either, in my OnModelCreating method in the database context, I always get an error "InvalidOperationException: The entity type 'MyEntityModel' requires a key to be defined."

What key am I supposed to define, tho? It's not as if a query has a key for its result entries, or does it/can I make it have one?

Unfortunately, I can't change the database to add new views, temp tables or whatever either, I (can) only have read access.

It might very well be I just haven't understood the concepts behind EF yet, but all tutorials/samples I look at just handle the most basic and simple cases, and my google-fu seems to fail me here as well.

Although it looks like working around the issue using basic connect-query-disconnect w/o EF might be a possibility, it seems to me going the DtabaseContext/EF way is more in line with ASP.NET Core's principles. Feel free to disagree or tell me otherwise if I'm wrong there.

Any samples that might show another way to make this work would be highly appreciated as well.

Answer 1

Okay, I figured it out myself. Seems asking the question helped my google fu. ;)

To anyone else running into the same problem:

My OnModelCreating function now looks like this

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<MyEntityType>(
        obj =>
        {
            obj.Property<int>(nameof(MyEntityType.ArbitraryPropertyIChoseAsId));
            obj.HasKey(nameof(MyEntityType.ArbitraryPropertyIChoseAsId));
        });
}

…and after a bit of fiddling to get the types of the properties right (who the hell defines a date column as numeric?!) it all works now. Yes, the properties' types of your Entity type must match the query result, and all query fields must appear as properties, I think. Of course you should have a "unique" property to serve as your arbitrary Key, but it doesn't have to be a database table key.

This all leads to a slightly ugly entity class; I handled that by making MyEntityType a wrapper class with an implicit conversion operator to the class I'll be using as the data model inside my application (which looks all nice and tidy and has proper types).

Answer 2

Using ASP.NET Core does not mean that you've to use Entity Framework core. You can use Full .NET Framework instead of .NET Core and Entity Framework Full instead of Entity Framework Core. There are several benefits with this:

1- ASP.NET Core 2 (Currently in beta) works well on .NET Standard 2. As both Full .NET Framework 4.6.1+ and .NET Core 2.0+ are implementation of .NET Standard 2.0, you can switch back to .NET Core 2 very easily when its stable release gets published.

2- Entity Framework Core 2.0 (Currently in beta) has a lots of enhancements over Entity Framework Core 1.1.1 . Working with version 2.0 will be far easier than 1.1.1 It has better query translation, better performance, fewer bugs, more methods etc.

So

Step 1 >> Develop app with ASP.NET Core and Full versions of EntityFramework & .NET Framework

Step 2 >> Target .NET Core 2 and Entity Framework Core 2 in your app when they stable releases becomes available.

It's a good idea to use database reverse engineering tool of Entity Framework full, and then change its result to make it compatible with EntityFramework Core.