Context

The book T-SQL Fundamentals Third Edition by Itzik Ben-Gan contains the following query in chapter 3:

SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid;

I've converted this to LINQ as follows:

var result =
    from customer in db.Customers
    join order in db.Orders
    on customer.Custid equals order.Custid into Abc
    from abc in Abc.DefaultIfEmpty()
    select new
    {
        customer.Custid,
        customer.Companyname,
        orderid = abc == null ? -1 : abc.Orderid
    };

Question

What's a good way to write the above using method syntax instead of query syntax?

I've started with this:

var result = db.Customers.Join(
    db.Orders,
    customer => customer.Custid,
    order => order.Custid,
    (customer, order) =>
        new
        {
            customer.Custid,
            customer.Companyname,
            orderid = order.Orderid
        }
    );

However, this of course leaves out the NULL valued items.

The part I'm not clear on is how to convert the into syntax into method syntax.

Any suggestions welcome!

Notes

The above query is in a project available here if you'd actually like to run the query yourself:

https://github.com/dharmatech/TSqlEf/blob/master/Chapter3p114/Program.cs

See the project readme for how to setup the database:

https://github.com/dharmatech/TSqlEf

CodePudding user response：

For left join in method syntax you need to use GroupJoin along with the method DefaultIfEmpty and SelectMany. Try to use the below query,

var result = db.Customers.GroupJoin(
    db.Orders,
    cust => customer.Custid,
    ord => order.Custid,
    (cust, ord) => new {cust, ord})
    .SelectMany(c => c.ord.DefaultIfEmpty(), (customer, order) =>
        new
        {
            customer.Custid,
            customer.Companyname,
            orderid = order.Orderid
        }
    ); 

CodePudding user response：

Ivan Stoev's suggestion

Ivan suggested in a comment above that this can be done using navigation properties. Here's a full approach based on his suggestion there:

var result = db.Customers.SelectMany(
    customer => customer.Orders.DefaultIfEmpty(),
    (customer, order) => new
    {
        customer.Custid,
        customer.Companyname,
        orderid = order == null ? -1 : order.Orderid
    });

So far, it does seem to be the simplest and most straightforward. Thanks Ivan!

CodePudding user response：

Abu's answer

Here's a version which is based upon Abu's answer. I had to add this conditional:

orderid = order == null? -1 : order.Orderid

to get it to work. I also changed some of the naming. However, it does appear to work!

var result = db.Customers.GroupJoin(
    db.Orders,
    customer => customer.Custid,
    order => order.Custid,
    (customer, orders) => new { customer, orders })
    .SelectMany(
        customer_orders => customer_orders.orders.DefaultIfEmpty(),
        (customer_orders, order) => new
        {
            customer_orders.customer.Custid,
            customer_orders.customer.Companyname,
            orderid = order == null? -1 : order.Orderid
        });

Similar question

See this similar question:

LEFT OUTER JOIN in LINQ

It contains an answer that is similar to Abu's approach.

CodePudding user response：

Caius Jard's suggestion

Caius mentioned that for report building purposes, a nested foreach approach can be taken. Below is an approach based on his suggestion:

foreach (var customer in db.Customers.Include(customer => customer.Orders))
{
    if (customer.Orders.Any())
    {
        foreach (var order in customer.Orders)
        {
            Console.WriteLine("{0} {1} {2}",
                customer.Custid,
                customer.Companyname,
                order.Orderid);
        }
    }
    else
    {
        Console.WriteLine("{0} {1} {2}",
            customer.Custid,
            customer.Companyname,
            -1);
    }
}