CODEWITHSUNDEEP
c#sqlsql-serverlinq
Mar_a
Mar_a

Reputation: 50

Convert Linq to SqlCommand

What is the equivalent of this Linq query in SQL Server query?

IEnumerable<Profile> profiles = am.Profile.Where(a => articles.Select(b => b.ProfileId).Distinct().Contains(a.ProfileID));

where articles is:

IEnumerable<Article> articles = (from a in am.Article
                                 orderby a.AddedDate descending
                                 select a).ToList();

Upvotes: 0

Views: 641

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460108

Use EXISTS:

SELECT * FROM Profile P
WHERE EXISTS(
    SELECT 1 FROM Article A
    WHERE A.ProfileId = P.ProfileId
)

Apart from that, your linq query is inefficient. Why do you use Distinct before Contains? It's not necessary to remove duplicates before you use Contains.

This is more readable and efficient:

IEnumerable<Profile> profiles = am.Profile
    .Where(p => articles.Any(a => a.ProfileID == p.ProfileID));

Upvotes: 3

Damien
Damien

Reputation: 8987

Select p.*
From [Profile] p
Where p.profileID In (
    Select Distinct ProfileId
    From [Article]
)

Upvotes: 0

King King
King King

Reputation: 63317

SELECT * FROM Profile
WHERE
EXISTS(SELECT DISTINCT ProfileId FROM Article WHERE ProfileId in (SELECT ProfileID FROM  Profile))

Upvotes: 1

Related Questions