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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With