CODEWITHSUNDEEP
sql-serverlinq
Jviaches
Jviaches

Reputation: 843

SQL to LINQ Expression

I have specific SQL expression :

{
select * from courceMCPD.dbo.Contact c 
where c.cID in ( select cId from courceMCPD.dbo.Friend f where f.cId=5)
}

i would like to get LINQ expression that gets the same result.

thank you in advance.

Upvotes: 2

Views: 124

Answers (3)

andleer
andleer

Reputation: 22578

Using labda expressions:

var query = dc.Contact
        .Where(c => dc.Friend.Select(f => f.cId).Contains(i.cID))
        .Where(c => c.cId == 5);

User "query" syntax:

var query = from c in dc.Contact
            where (from f in dc.Friend select f.cID).Contains(c.cId)
            where c.cId == 5
            select c;

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1503419

That sounds like it's equivalent to something like:

var friendIds = from friend in db.Friends
                where friend.ContactId == 5
                select friend.ContactId;

var query = from contact in db.Contacts
            where friendIds.Contains(contact.Id)
            select contact;

(There are lots of different ways of representing the query, but that's the simplest one I could think of.)

It's pretty odd to perform a join on a particular field and also mandate that that field has to have a particular value though... there's not very much difference between that and:

var query = db.Contacts.Where(c => c.Id == 5);

... the only difference is whether there are any friend entries for that particular contact.

EDIT: Smudge gave another option for the query in a comment, so I'm promoting it into this answer...

var query = db.Contacts.Where(c => c.Friends.Any(f => f.cId == 5))

This assumes you've got an appropriate Friends relationship defined in the Contacts entity.

Upvotes: 4

Smudge202
Smudge202

Reputation: 4687

You haven't specified VB/C# so I'm going for VB =P

        Dim results As IEnumerable(Of yourEntities.Contact) = 
        (From c In yourContextInstance.Contacts Where (From f In yourContextInstance.Friends 
                                                       Where f.cId = 5 Select f.cId).Contains(c.cID))

Clearly, Jon's answer works, this query (I believe) just resembles your T-SQL Closer.

Upvotes: 1

Related Questions