How to join tables in EF LINQ

Question

When I try to join tables

var query =
    from foo in db.Foos
    from bar in db.Bars
    where foo.ID == bar.FooID
    where foo.ID == 45
    select bar;


query.toArray()

I get such error

Unable to create a constant value of type 'Bar'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Martyrize

Answer 1

Try that instead:

var query =
    from foo in db.Foos
    join bar in db.Bars on foo.ID equals bar.FooID
    where foo.ID == 45
    select bar;

Anyway, I suggest you model the relation between Foo and Bar in the EDM designer, this way you don't need an explicit join:

var query =
    from foo in db.Foos
    where foo.ID == 45
    from bar in foo.Bars
    select bar;

Irizarry

will modeling the relation in EDM designer create a foreign key in the database also? – Holophrastic

how to convert it to LINQ Method-Based? I don't have idea how to convert from bar in foo.Bars using Method-Based – Rosco

@Rosco db.Foos.Where(foo => foo.ID == 45).SelectMany(foo => foo.Bars) – Irizarry