-1

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

dharmatech
  • 8,979
  • 8
  • 42
  • 88
  • @CaiusJard, well not only was it missing `DefaultIfEmpty`, it also wasn't using `GroupJoin` which appears to be required. – dharmatech Nov 07 '21 at 08:12
  • 1
    @CaiusJard, Thank you for the suggestion regarding posting answers instead of editing. I've removed the answer from the question and added it as a separate answer. – dharmatech Nov 07 '21 at 12:26
  • 1
    It's "required" only in the sense that Join discards non matches (customers without any orders) whereas GroupJoin can give a "customer_with_list_of_orders_that_may_be_empty" and can then be used to emulate how a database left joins, because you have this "single master with multiple details in a list" structure that can be axpanded by iteration into "N masters with N details, one repeated master per detail" – Caius Jard Nov 07 '21 at 12:37

4 Answers4

1

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
        }
    );
Zafor
  • 347
  • 2
  • 8
  • Abu, wow, this is impressive, thank you! I updated my question to include a version based on your answer. See there for what I ended up using. – dharmatech Nov 07 '21 at 07:21
  • 1
    Let me know if you have a bitcoin address and I'll be happy to send you something to buy lunch. :-D – dharmatech Nov 07 '21 at 07:21
  • @AluanHaddad, Abu's answer pretty much worked after a couple of changes (I updated my question with a version of his answer which does seem to work.) Just curious, what is your objection? Do you have another suggestion for how to approach this? – dharmatech Nov 07 '21 at 07:22
  • 1
    You don't need the `SelectMany. See the `GroupJoin` overload https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.groupjoin?view=net-5.0#System_Linq_Enumerable_GroupJoin__4_System_Collections_Generic_IEnumerable___0__System_Collections_Generic_IEnumerable___1__System_Func___0___2__System_Func___1___2__System_Func___0_System_Collections_Generic_IEnumerable___1____3__ – Aluan Haddad Nov 07 '21 at 07:24
  • @AluanHaddad, It would be interesting to see a version without `SelectMany`. Feel free to post an answer that illustrates this approach. :-) – dharmatech Nov 07 '21 at 07:29
  • @AluanHaddad, without the `SelectMany`, `GroupJoin` returns a bunch of groupings. So how would you get the non-grouped items without flattening them out via `SelectMany`? – dharmatech Nov 07 '21 at 07:30
  • It depends how you use them. There's no point unpacking them to a flat list when you can just use them in some nested loops style at the moment of output, for example – Caius Jard Nov 07 '21 at 07:57
  • 1
    (But I too am curious to know what Aluan means) – Caius Jard Nov 07 '21 at 08:07
  • 1
    Aluan is wrong, `GroupJoin` is not enough. It could be done with only `SelectMany` and correlated subquery though. However, all these are general LINQ approaches, while in EF things are much simpler when using navigation properties instead of manual joins as it should. Compare all these to `db.Customers.SelectMany(customer => customer.Orders.DefaultIfEmpty(), (customer, order) => new { ... })` – Ivan Stoev Nov 07 '21 at 11:05
  • 1
    @IvanStoev, Ah... I see. That's very nice. I've posted an answer based on your suggestion. Thank you so much! – dharmatech Nov 07 '21 at 12:22
1

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!

dharmatech
  • 8,979
  • 8
  • 42
  • 88
  • As an extension to this, you might not even need to run the join. Say you're preparing a report, you can just do `foreach(var c in db.Customers.Include(c => c.Orders)){ foreach(var o in c.Orders) { Console.Writeline$("Customer {c.Id} has order {o.Id}"); } }`, maybe with an `if` to see if `!c.Orders.Any()` then the customer has no orders.. In essence, I'm saying that if you're generating some rectngular block of data where customer is repeated over and over, purely so you can then do `foreach(var x in ..someCustomerOrderJoin..)` then you might not need to.. you can just nested loop it – Caius Jard Nov 07 '21 at 12:33
  • @CaiusJard Ah, OK. I've added another answer based on your suggestion. Thank you! https://stackoverflow.com/a/69872705/268581 – dharmatech Nov 07 '21 at 13:06
0

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.

dharmatech
  • 8,979
  • 8
  • 42
  • 88
0

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);
    }
}
dharmatech
  • 8,979
  • 8
  • 42
  • 88