1

Just starting out with Entity Framework (Code First) and I have to say I am having a lot of problems with it when loading SQL data that is fairly complex. For example, let's say I have the following tables which stores which animals belongs to which regions in the world and the animal are also categorized.

Table: Region
Id: integer
Name string

Table AnimalCategory
Id integer
Name: string
RegionId: integer  -- Refers back Region

Table Animal
Id integer
AnimalCategoryId integer -- Refers back AnimalCategory

Let's say I want to create a query with Entity Framework that would load all Animals for a specific region. The easiest thing to do is to create 3 Entities Region, AnimalCategory, and Animal and use LINQ to load the data.

But let's say I am not interested in loading any AnimalCategory information and define an Entity class just to represent AnimalCategory so that I can do the JOIN. How can I do this with Entity Framework? Even with many of its Mapping functions I still don't think this is possible.

In non Entity Framework solutions this is easy to accomplish by using INNER JOINs in SPs or inline SQL. So what are my options in Entity Framework? Shall I pollute my data model with these useless tables just so I can do a JOIN?

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
John C
  • 11
  • 1
  • Where's the pollution ? Either you relate animals directly to regions (with a many to many relationship, probably => you don't need an intermediate Entity, even if an intermediate table will exist in your db), or you want an intermediate AnimalCategory. In Sql, you'll need AnimalCategory table for this case. Is this a pollution ? – Raphaël Althaus Mar 16 '15 at 16:03
  • It's pollution if you have to define a Entity just to do a JOIN. Imagine you don't control how it is stored in the database. You are just a consumer of the data and all you are interested in is the Region and list of Animals associated with that Regions. You don't care about anything in between. So why define Entity classes and make them visible to your application layer? Now you could say you that your Database Entities/Models are different than your Application Domain Models and there's another translation that occurs between them. That's all fine but then there's potential duplication. – John C Mar 16 '15 at 17:06
  • If those tables are useless, then I am not sure why are they there in the first place! If you are anyways going to use these tables for inner join in inline SQL or SP then why not use them through EntityFramework ? It is really not clear what you are expecting from EF. Without these entities in place there is no way you could figure out which animal belongs to which region. Can you explain how it can be done in inline SQL without these tables? – Nilesh Mar 17 '15 at 09:06
  • Nilesh, that's simple. You would just do SELECT a.* FROM Animal a INNER JOIN AnimalCategory ac ON a.AnimalCategoryId = ac.Id WHERE ac.RegionId = @RegionId. My use of the word of "useless" is I guess a bit strong. Those intermediate tables may be useful for other consumers but let's just say for the sake of my application those intermediate tables are not used at all except for the purpose of doing the JOINs. And my point is that it appears Entity Framework forces me to create these Entities just to do that JOIN. – John C Mar 17 '15 at 18:59

1 Answers1

0

It's a matter of choice I guess. EF choose to support many-to-many associations with transparent junction tables, i.e. where junction tables only have two foreign keys to the associated entities. They simply didn't choose to support this far less common "skipping one-to-many-to-many" scenario in a similar manner.

And I can imagine why.

To start with, in a many-to-many association, the junction table is nothing but that: a junction, an association. However, in a chain of one-to-many (or many-to-one) associations it would be exceptional for any of the involved tables to be just an association. In your example...

Animal → AnimalCategory → Region

...AnimalCategory would only have a primary key (Id) and a foreign key (RegionId). That would be useless though: Animal might just as well have a RegionId itself. There's no reason to support a data model that doesn't make sense.

What you're after though, is a model in which the table in the middle does carry information (AnimalCategory.Name), but where you'd like to map it as a transparent junction table, because a particular class model doesn't need this information.

Your focus seems to be on reading data. But EF has to support all CRUD actions. The problem here would be: how to deal with inserts? Suppose Name is a required field. There would be no way to supply its value.

Another problem would be that a statement like...

region.Animals.Add(animal);

...could mean two things:

  1. add an Animal and a new AnimalCategory, the latter referring to the Region.
  2. Add an Animal referring to an existing AnimalCategory - without being able to choose which one.

EF wouldn't want to choose for some default behavior. You'd have to make the choice yourself, so you can't do without access to AnimalCategory.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • That's good point Gert. For full CRUD support, I guess you will have to define all the necessary Entities that are needed for insert/update. So do people who typically use EF define db/persistence models and separate application models? It's sounds like that's almost a necessity in order hide certain database implementation details from the application logic code. – John C Mar 17 '15 at 19:06
  • Not *typically*, no. But it's done. I do it myself whenever appropriate but I prefer to use the EF classes whenever feasible. See also http://stackoverflow.com/q/18109547/861716. – Gert Arnold Mar 17 '15 at 20:01