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.

@fhlamarche: He said he don't have write access to the DB, so can't add views. Why even using EFCore in such a case? Dapper seems more suitable for your use case though https://github.com/StackExchange/dapper-dot-net. EF is only good if you map databases to objects and use Linq for querying. w/o it, EF is of little use — Tseng, Aug 03 '16 at 14:00
@Tseng: I'm pretty new to .Net, and it seemed like the preferred way for ASP.NET Core applications to get info from databases, in fact, I didn't read about dapper in ANY of the google search results (and I searched for quite some time!) I believe when you say that would suit it better, but I had not found out about it when I asked the question ;) I'll have a look at it tomorrow and see if I'll switch over to it or stay with the (working now too, see below) EF code I have now. — Johann Studanski, Aug 03 '16 at 15:05
Dapper is a lightweight ORM Framework, which allows you to directly query the db and bind the results to a model w/o all the LINQ Fluid query syntax and strong typed queries (just strong typed results), which seems the exact fit for your case and for such cases has a way better performance and with less configuration overhead. IIRC StackExchange/Stackoverflow use (and develops) it — Tseng, Aug 03 '16 at 16:28

score 0 · Answer 1 · answered Aug 03 '16 at 15:01

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).

Since it's a read-only DB, you might wanna override SaveChanges() and have it throw an exception, just in case. — 0xFF, Aug 03 '16 at 15:09
@fhlamarche: Good idea, thanks! I connected via ReadOnly, but that will be an additional safeguard, I'll override any writing operation I find. — Johann Studanski, Aug 03 '16 at 15:28

score 0 · Answer 2 · answered Jun 10 '17 at 06:16

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.

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

2 Answers2