Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework - How do I join tables on non-primary key columns in secondary tables?

I want to join 2 tables using entity framework. I want the join to the second table to be on a non-primary key column.

e.g. I have a table Foo with fields

Foo.Id (PK)
Foo.DbValue

and table Bar

Bar.Id (PK)
Bar.DbValue
Bar.Description

And I want to join Foo to Bar in EF on the DbValue field.

In hibernate/nhibernate one can do this by adding a column parameter to a many-to-one. roughly like this

<class name="Foo" table="Foo>
  <id name="Id" column="Id" />
  <many-to-one name="Bar" class="Bar" column="**DbValue**" />
</class>

Thanks in advance if anyone knows how to do this in EF.

like image 489
MarkGr Avatar asked Feb 17 '10 20:02

MarkGr


People also ask

Is primary key necessary for join table?

A table must have exactly one primary key to qualify as relational, but that key can be composed of multiple columns. A foreign key, by contrast, is one or more fields or columns that corresponds to the primary key of another table. Foreign keys are what make it possible to join tables to each other.

How do I join two tables in an entity?

Method Syntax Next, use the join extension method and specify the inner table as the first argument to the join method. . Join(db. EmailAddresses, The next two arguments specify the condition on which you want to join the tables.

Can we have table without primary key in Entity Framework?

The Entity framework will not support to have a table without primary key, but we can overcome this issue by accessing the table with additional column via a view and marking the new column as Primary in entity framework. Entity Framework requires primary keys for entities.


1 Answers

Well you can't do this as a named relationship (i.e. the standard way).

So this means the relationship is NOT part of the model.

However you can still do a standard LINQ join though:

from f in ctx.Foo
join b in ctx.Bar on f.DbValue equals b.DbValue
select new {f,b} 

Hope this helps

Check out my EF Tips series.

like image 148
Alex James Avatar answered Oct 23 '22 10:10

Alex James