Translating SQL group-by query to LINQ query

Question

I'm having troubles with trying to translate an SQL query to LINQ. Suppose, we have the following data structure:

class Movie
{
    public Guid ID { get; set; }

    // navigation properties
    public virtual ICollection<Commercial> Commercials { get; set; }
    public virtual ICollection<Spectator> Spectators { get; set; }
}

class Commercial
{
    public Guid ID  { get; set; }
    public Guid MovieID { get; set; }
    public string ProductType { get; set; }

    // navigation property
    public virtual Movie Movie { get; set; }
}

class Spectator
{
    public Guid ID  { get; set; }
    public Guid MovieID { get; set; }
    public int Age { get; set; }

    // navigation property
    public virtual Movie Movie { get; set; }
}

Now let's say, I want to find out, how many spectators saw a commercial for a certain product category. In SQL, it would look like this:

select Commercial.ProductType, count(distinct Spectator.ID)
from Spectator
join Movie on Spectator.MovieID = Movie.ID
join Commercial on Commercial.MovieID = Movie.ID
where Spectator.Age > 60 # optional filter
group by Commercial.ProductType;

First, I tried to use the GroupBy() function, but I didn't find a way to group the spectators by the commercial's product types due to the many-to-many relationship.

Then I tried something like:

var query = db.Commercials.Where(x => x.Age > 60).GroupJoin(
    db.Spectators,
    c => c.MovieID,
    s => s.MovieID,
    (c, g) => new { ProductType = c.ProductType, Count = g.Distinct().Count() });

This looked promising, but didn't return the expected results.

Answer 1

You have all these nice navigation properties, so you don't need to join in LINQ. A navigation property can be viewed as a hard-coded join, which prevents repetitive, verbose, and error-prone code (for example, using the wrong join properties).

Knowing this, you can think of the query to get your data. It's not as trivial as it seems (and as I first thought).

There can be n commercials in one movie, so if you simply count the spectators per movie and commercial the result is too high (n times the number of spectators). You have to count unique spectators. And these counts should be grouped by ProductType. That brings us to this query:

var query = from c in db.Commercials
            group c by c.ProductType into cgroup
            select new
            {
                ProductType = cgroup.Key,
                NumberOfSpectators = cgroup.SelectMany(c => c.Movie.Spectators
                     .Where(s => s.Age > 60)
                     .Select(s => s.Id)).Distinct()).Count()
            };

Answer 2

i have done small sample.

public class Letter
{
    public int Id { get; set; }
    public int SenderId { get; set; }
}
public class Sender
{
    public int Id { get; set; }
    public string Country { get; set; }
}
public class Receiver
{
    public int Id { get; set; }
    public int LetterId { get; set; }
    public string Country { get; set; }
}
class StackOverflow_SQLtoLinq
{
    static void Main(string[] args)
    {
        List<Letter> lstLetters = new List<Letter>() { 
        new Letter(){Id=1,SenderId=1},
        new Letter(){Id=2,SenderId=2},
        new Letter(){Id=3,SenderId=3}
        };

        List<Sender> lstSenders = new List<Sender>() {
        new Sender(){Id=1,Country="IND"}, 
        new Sender(){Id=2,Country="US"},  
        new Sender(){Id=3,Country="NZ"}
        };

        List<Receiver> lstReceivers = new List<Receiver>() { 
        new Receiver(){Id=1,LetterId=1,Country="IND"},
        new Receiver(){Id=2,LetterId=11,Country="US"},
        new Receiver(){Id=3,LetterId=1,Country="NZ"},
        };

        var data = (from receiver in lstReceivers
                   join letter in lstLetters on receiver.LetterId equals letter.Id 
                   join sender in lstSenders on letter.SenderId equals sender.Id
                    group sender by new { id = sender.Id, country = sender.Country } into finalData
                   select new
                   {
                       country = finalData.Key.country,
                       Count = finalData.Distinct().Count()
                   }).ToList();

    }
}

finally in have a data in data variable.

Answer 3

Do you have relation between your database? If yes your model that generated by entity framework must be like this:

 public class Receiver
    {
        public int LetterId { set; get; }
        public Letter Letter { set; get; }
        public Country Country { set; get; }
    }
    public class Letter
    {
        public int Id { set; get; }
        public int SenderId { set; get; }
        public Sender Sender { set; get; }
        public IEnumerable<Receiver> Receivers { set; get; }

    }
    public class Sender
    {
        public int Id { set; get; }
        public Country Country { set; get; }
        public IEnumerable<Letter> Letters { set; get; }
    }
    public class Country
    {
        public int Id { set; get; }
    }

then you have 3 types from your context:

IEnumerable<Receiver> receivers = new List<Receiver>();
IEnumerable<Letter> leters = new List<Letter>();
IEnumerable<Sender> senders = new List<Sender>();

so your response is like:

var results = from receiver in receivers
        from letter in leters
        from sender in senders
        where receiver.LetterId == letter.Id &&
              sender.Id == letter.SenderId
        select
            new Result
            {
                Country = sender.Country,
                CountOfCountry = sender.Letters.Select(x => x.Receivers).Distinct().Count()
            };

that ResultClass is :

public class Result
    {
        public Country Country { set; get; }
        public int CountOfCountry{ set; get; }
    }

If you put your class diagram I can help you better!