0

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, O.orderdate FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON O.custid = C.custid AND O.orderdate = '20160212';

Note that the join clause has two conditions:

O.custid = C.custid AND O.orderdate = '20160212'

Techniques demonstrated in other posts

The following posts (among others) demonstrate how to use multiple conditions with a JOIN

LINQ to Entity : Multiple join conditions

LINQ Join with Multiple Conditions in On Clause

The issue

Based on the posts shared above, here's what I came up with for an EF Core version of the query:

var result = from customer in db.Customers join order in db.Orders on new { Key1 = customer.Custid, Key2 = true } equals new { Key1 = order.Custid, Key2 = order.Orderdate == new DateTime(2016, 2, 12) } into Abc from abc in Abc.DefaultIfEmpty() select new { customer.Custid, customer.Companyname, Orderid = abc == null ? -1 : abc.Orderid, Orderdate = abc == null ? new DateTime() : abc.Orderdate };

However, with that code, a red squiggly comes up on join with the following message:

enter image description here

CS1941: The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.

Link to the compiler error on learn.microsoft.com:

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/compiler-messages/cs1941?f1url=%3FappId%3Droslyn%26k%3Dk(CS1941)

Question

I've modeled my version after the examples shown in the other posts. So not sure what in my example is causing the issue.

What's a good way to setup the LINQ query for the given SQL call?

Thanks!

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/Chapter3e7/Program.cs

See the project readme for how to setup the database:

https://github.com/dharmatech/TSqlEf

Cly's suggestion

Here's an approach based on Cly's answer which uses a where clause:

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() where abc.Orderdate == new DateTime(2016, 2, 12) select new { customer.Custid, customer.Companyname, Orderid = abc == null ? -1 : abc.Orderid, Orderdate = abc == null ? new DateTime() : abc.Orderdate };

If I use the following to output the results:

foreach (var item in result) { Console.WriteLine("{0} {1} {2}", item.Custid, item.Companyname, item.Orderid, item.Orderdate); } Console.WriteLine(); Console.WriteLine("{0} rows", result.Count());

I get the following:

48 Customer DVFMB 10883 45 Customer QXPPT 10884 76 Customer SFOGW 10885 3 rows

Whereas if I run the original SQL code in SSMS, I get 91 rows.

The exercise description in the book also states that 91 rows are expected. Here's the exercise text and expected output:

enter image description here

Here's the solution text which goes into the subtleties behind the join and why where isn't applicable in this case:

enter image description here

Improve this question
5
  • 1
    I would try to remove Key2 part from join and add order.Orderdate == new DateTime(2016, 2, 12) as where. It is not a joining expression at all but a filter on one side of the joined entities. Commented Nov 8, 2021 at 22:59
  • @cly, Thanks for the suggestion! Where would you say the where clause should be inserted? I've tried to put it in the query at various places and they all lead to errors. Commented Nov 8, 2021 at 23:04
  • 1
    Try from abc in Abc.Where(abc => abc.Orderdate == new DateTime(2016, 2, 12)).DefaultIfEmpty() Commented Nov 9, 2021 at 0:31
  • @Charlieface, Omgosh... that appears to work perfectly! I've added an answer that illustrates the approach that you suggest: stackoverflow.com/a/69892036/268581 Very interesting... thank you! Commented Nov 9, 2021 at 2:14
  • @Charlieface, if you decide to add your own answer based on your suggestion, I'll vote yours up and remove mine. :-) Commented Nov 9, 2021 at 2:30

3 Answers 3

Reset to default
1

Getting out the Key2 not-so-join-but-a-side-filter part into where looks like this:

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() where abc.Orderdate == new DateTime(2016, 2, 12) select new { customer.Custid, customer.Companyname, Orderid = abc == null ? -1 : abc.Orderid, Orderdate = abc == null ? new DateTime() : abc.Orderdate };
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for the clarification cly! So, the output I get from this version (3 rows) is different from what the book expects (91 rows). I've updated the question with a section which demonstrates your approach and included notes from the book which clarify why where leads to a different result.
The new details show different requirements. There is a modifier in "solution query" which you didnt used yet :) Find it's place in my version and write it with appropriate syntax. Learning by trial and error is a very good thing! :)
Hmm... Interesting hint! When you say there's a modifier in the query that's not in your version, are you referring to AND?
It looks like Charlieface's suggestion appears to work. I've added answer based on that. Is the approach there similar to what you had in mind?
1

Simply use a filtered include: https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager#filtered-include

Improve this answer

Comments

0

Charlieface's suggestion

Here's an approach based on Charlieface's suggestion in a comment above. It does indeed appear to work!

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

Using the following to output the result:

foreach (var item in result) { Console.WriteLine("{0,3} {1} {2,6} {3,10}", item.Custid, item.Companyname, item.Orderid == -1 ? "NULL" : item.Orderid, item.Orderid == -1 ? "NULL" : item.Orderdate.ToString("yyyy-MM-dd")); }

shows the following:

 72 Customer AHPOP NULL NULL 58 Customer AHXHT NULL NULL 25 Customer AZJED NULL NULL 18 Customer BSVAR NULL NULL 91 Customer CCFIZ NULL NULL 68 Customer CCKOT NULL NULL 49 Customer CQRAA NULL NULL 24 Customer CYZTN NULL NULL 22 Customer DTDMN NULL NULL 48 Customer DVFMB 10883 2016-02-12 10 Customer EEALV NULL NULL 40 Customer EFFTC NULL NULL 85 Customer ENQZT NULL NULL 82 Customer EYHKM NULL NULL 79 Customer FAPSM NULL NULL 17 Customer FEVNN NULL NULL 37 Customer FRXZL NULL NULL 33 Customer FVXPQ NULL NULL 53 Customer GCJSG NULL NULL 39 Customer GLLAG NULL NULL 16 Customer GYBBY NULL NULL 4 Customer HFBZG NULL NULL 5 Customer HGVLZ NULL NULL 42 Customer IAIJK NULL NULL 34 Customer IBVRG NULL NULL 63 Customer IRRVL NULL NULL 73 Customer JMIKW NULL NULL 15 Customer JUWXK NULL NULL 50 Customer JYPSC NULL NULL 3 Customer KBUDE NULL NULL 21 Customer KIDPX NULL NULL 30 Customer KSLQF NULL NULL 55 Customer KZQZT NULL NULL 71 Customer LCOUJ NULL NULL 77 Customer LCYBZ NULL NULL 66 Customer LHANT NULL NULL 38 Customer LJUCA NULL NULL 59 Customer LOLJO NULL NULL 36 Customer LVJSO NULL NULL 64 Customer LWGMD NULL NULL 29 Customer MDLWA NULL NULL 2 Customer MLTDN NULL NULL 78 Customer NLTYP NULL NULL 84 Customer NRCSK NULL NULL 1 Customer NRZBB NULL NULL 65 Customer NYUHS NULL NULL 44 Customer OXFRU NULL NULL 12 Customer PSNMQ NULL NULL 47 Customer PSQUZ NULL NULL 51 Customer PVDZC NULL NULL 52 Customer PZNLA NULL NULL 56 Customer QNIVZ NULL NULL 8 Customer QUHWH NULL NULL 67 Customer QVEPD NULL NULL 45 Customer QXPPT 10884 2016-02-12 7 Customer QXVLA NULL NULL 60 Customer QZURI NULL NULL 19 Customer RFNQC NULL NULL 9 Customer RTXGC NULL NULL 76 Customer SFOGW 10885 2016-02-12 69 Customer SIUIH NULL NULL 86 Customer SNXOJ NULL NULL 88 Customer SRQVM NULL NULL 54 Customer TDKEG NULL NULL 20 Customer THHDP NULL NULL 70 Customer TMXGN NULL NULL 11 Customer UBHAU NULL NULL 43 Customer UISOJ NULL NULL 35 Customer UMTLM NULL NULL 26 Customer USDBG NULL NULL 13 Customer VMLOG NULL NULL 80 Customer VONTK NULL NULL 62 Customer WFIZJ NULL NULL 27 Customer WMFEA NULL NULL 14 Customer WNMAF NULL NULL 61 Customer WULWD NULL NULL 57 Customer WVAXS NULL NULL 23 Customer WVFAF NULL NULL 90 Customer XBBVR NULL NULL 6 Customer XHXJV NULL NULL 41 Customer XIIWM NULL NULL 75 Customer XOJYP NULL NULL 46 Customer XPNIK NULL NULL 28 Customer XYUFB NULL NULL 89 Customer YBQTI NULL NULL 31 Customer YJCBX NULL NULL 81 Customer YQQWW NULL NULL 74 Customer YSHXL NULL NULL 32 Customer YSIQX NULL NULL 87 Customer ZHYOS NULL NULL 83 Customer ZRNDE NULL NULL 91 rows
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.