Linq to Entities How to return all records from Parent?

Question

Employee table

Employee_ID int
Employee_Name varchar(50)

Sales table:

Sales_ID int
Employee_ID int
Sale_Amount money

Standard SQL select

Select
*
From Employee emp
left outer join
Sales s
on
s.Employee_ID = emp.Employee_ID

Standard SQL Results (The exact results I want using Linq to Entites)

1 Emp1 1 1 150.00
1 Emp1 2 1 500.00
2 Emp2 3 2 250.00
3 Emp3 NULL NULL NULL
4 Emp4 NULL NULL NULL
5 Emp5 4 5 700.00

Now to tackle Linq To Entities

            Dim query = From emp In entiites.Employee _
                    From sales In emp.Sales _
                    Select _
                        emp, _
                        sales

Linq To Entities Result (Where is Employee_ID 3 and 4)

1: Emp1: 150.0000
1: Emp1: 500.0000
2: Emp2: 250.0000
5: Emp5: 700.0000

Try it with Linq to Entities with a left outer join:

            Dim query = From emp In entiites.Employee _
                    Group Join sales In entiites.Sales _
                    On emp.Employee_ID Equals sales.Employee.Employee_ID _
                    Into sales_grp = Group _
                    From Sel_SalesGrp In sales_grp.DefaultIfEmpty() _
                    Select _
                        emp, _
                        Sel_SalesGrp

Then I get this error using DefaultIfEmpty:

LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable1[m12Model.Sales] DefaultIfEmpty[Sales](System.Collections.Generic.IEnumerable1[m12Model.Sales])' method, and this method cannot be translated into a store expression.

Linq to Entites: does not support DefaultIfEmpty().

Answer 1

You need to perform a left join by using the "Into" and "DefaultIfEmpty()" keywords:

Dim query = From emp In entities.Employee _
        Join sal In entities.Sales On emp.Employee_ID Equals sal.Employee_ID Into LeftJoinSal _
        From ljs In LeftJoinSal.DefaultIfEmpty() _
        Select _
            emp, _
            ljs

This site shows another simple example: GeeksWithBlogs. Though it is in C#.

The key is that you need to join into a new name to prevent it from filtering the results from your first table (which hides the rows that don't join), then select from that using the DefaultIfEmpty() function, which provides a default (null) value in the cases where there is no join.

Answer 2

How about this:

Dim query = From emp In entiites.Employee Join sales In In entiites.Sales _
    On emp.Employee_ID Equals sales.Employee_ID select new { emp, sales }

Instead of "select new { emp, sales }" you can select the columns you want. That should be a standard left join using LINQ.

Answer 3

Hi,

You probably have found a solution by now, but I have a suggestion, using a different approach than joins:

    Dim db As New TestEntities
    Dim EmptySale As Decimal? = Nothing
    Dim Q = (From emp In db.Employee From sale In emp.Sale _
             Select emp.Name, amount =sale.SaleAmount).ToList
    Dim Q2 = (From emp In db.Employee Where Not emp.Sale.Any _
             Select emp.Name, amount = EmptySale)
    Dim Q3 = From emp In Q.Union(Q2) Order By emp.Name, emp.amount

    For Each e In Q3
        Console.WriteLine("{0} {1}", e.Name, e.amount)
    Next

The result of this is:

Emp1 150,00
Emp1 500,00
Emp2 250,00
Emp3
Emp4
Emp5 750,00

I hope this can help you

Answer 4

Using answer 3, when I replace this query with my own entity names, I get "End of Statement expected" at the Into.