CODEWITHSUNDEEP
c#sqlentity-frameworklinq
Tom
Tom

Reputation: 16236

Simple Sql statement in Linq?

Table: Users
ID123   John Doo
ID345   John Woo

Table: Contacts
ID123  Microsoft  [email protected]  +1-234567
ID345  Private Co [email protected]  +9-654321 
ID345  IBM        [email protected]        +1-456789

Run SQL:

SELECT FirstName, LastName, CompanyName 
FROM Users INNER JOIN Contacts ON User.Id = Contacts.UserId
WHERE (FirstName = 'John' AND CompanyName = 'Microsoft') OR CompanyName = 'IBM'

We get these results:

John   Doo   Microsoft
John   Woo   IBM

Pretty simple so far, right? Now, let's write this in C# Lambda or Linq:

conn.Users.Where(user => 
(user.FirstName=="John" && user.Contacts.Any(contact => contact.CompanyName=="Microsoft"))
|| user.Contacts.Any(contact => contact.CompanyName=="IBM"))
.Select(......);

My question is, what do we put in the Select() statement at the end to achieve the same result set?

Upvotes: 0

Views: 46

Answers (1)

Rob
Rob

Reputation: 27357

ON User.Id = Contacts.UserId indicates that you've got a 1..* relationship from User to Contact.

That means with Linq, you must start by querying Contacts, rather than Users, as the result will have a row per contact, rather than a row per user.

Something like this will work:

conn.Contacts
     .Where(c => c.CompanyName == "IBM" ||
                (c.CompanyName == "Microsoft" && c.User.FirstName == "John"))
     .Select(c => new { c.User.FirstName, c.User.LastName, c.CompanyName })

Upvotes: 1

Related Questions