CODEWITHSUNDEEP
c#asp.net-mvclinqentity-framework
vpascoal
vpascoal

Reputation: 278

Translate SQL query to LINQ (subqueries)

Hi have a site where users can vote on images. The votes are stored in table Votes where I store the Id of the submission (FK_id)


The tables are:

Table Submissions
Id (uniqueidentifier)
Name (varchar(50))
Image (varchar(50))

Table Votes
Id (int)
Fk_id (foreign key to Submissions.Id)

I'm new to linq so I don't know how to translate this:

SELECT *, 
    isnull((SELECT count(*) 
    FROM Votes
    WHERE Fk_id = S.Id      
    GROUP BY Fk_id),0) as Votes 
FROM Submissions S

I wanted something like this:

List<Model> = (from p in ????
               select new Model
               {
                   Name = p.Name,
                   Image = p.Image,
                   Votes = p.Votes               
               }).ToList();

Thank you.

Upvotes: 1

Views: 316

Answers (3)

Andriy Buday
Andriy Buday

Reputation: 1989

This would be translation of your SQL code, which also takes into account isnull ensuring that there is model for each submission:

List<Model> list = (from s in Submissions
        select new Model
        {
            Id = s.Id,
            Name = s.Name,
            Image = s.Image,
            Votes = (from v in Votes
                     where v.Fk_id == s.Id
                     group v by v.Fk_id into g
                     select g.Count()).FirstOrDefault()
        }).ToList();

If you are using Entity Framework you should ensure that your data model is generated correctly. In this case your Submission entity would already contain Votes as property. Then everything is easy as:

List<Model> list = (from s in context.Submissions
                    select new Model
                    {
                        Id = s.Id,
                        Name = s.Name,
                        Image = s.Image,
                        Votes = s.Votes.Count()
                    }).ToList();

Upvotes: 1

sgmoore
sgmoore

Reputation: 16077

Rather than translate your Sql (which I think is overly complicated), don't you just want to do something like : 

List<Model> = (from p in Submissions 
              select new Model
              {
                 Name = p.Name,
                 Image = p.Image,
                 Votes = p.Votes.Count() 
              }).ToList();

Upvotes: 3

Servy
Servy

Reputation: 203840

Seems like a fairly straight port to me, other than the order of select and from being reversed.

Submissions.Select(sub => new
{
  //...other fields
  Votes = Votes.Where(vote => vote.Fk_id == sub.Id)
               .GroupBy(vote => vote.Fk_id)
               .Select(group => group.Count()),
}

Now, looking at the query you could probably just do the group by on Votes directly, rather than as an inner query, and it should be quite a bit more efficient, but that's entirely up to you.

Upvotes: 1

Related Questions