Issue On The Linq Query

Question

I am trying to do a very simple task with Linq and already stuck. It's really horrible but I guess, it's better to know using this post. I've two tables. One is Products and another is Ratings. Here is the demo script:

CREATE TABLE [dbo].[Products](
    [ProductID] [int] IDENTITY(1,1) PRIMARY KEY,
    [ProductName] [varchar](40) NULL,
    [Price] [float] NULL,
    [Details] [varchar](max) NULL,
    [CategoryID] [int] NULL
 )

INSERT [dbo].[Products] ([ProductID], [ProductName], [Price], [Details], [CategoryID]) VALUES (1, N'Denim', 1200, NULL, 1)
INSERT [dbo].[Products] ([ProductID], [ProductName], [Price], [Details], [CategoryID]) VALUES (2, N'Denim 2', 220, NULL, 1)
INSERT [dbo].[Products] ([ProductID], [ProductName], [Price], [Details], [CategoryID]) VALUES (3, N'Pringles', 240, NULL, 2)
INSERT [dbo].[Products] ([ProductID], [ProductName], [Price], [Details], [CategoryID]) VALUES (4, N'Pringles 2', 260, NULL, 2)
INSERT [dbo].[Products] ([ProductID], [ProductName], [Price], [Details], [CategoryID]) VALUES (5, N'Pringles 3', 240, NULL, 2)

CREATE TABLE [dbo].[Ratings](
    [AutoId] [int] IDENTITY(1,1) PRIMARY KEY,
    [ProductId] [int] NOT NULL,
    [UserRating] [float] NOT NULL
)

INSERT [dbo].[Ratings] ([AutoId], [ProductId], [UserRating]) VALUES (4, 2, 1.5)
INSERT [dbo].[Ratings] ([AutoId], [ProductId], [UserRating]) VALUES (5, 4, 2.5)
INSERT [dbo].[Ratings] ([AutoId], [ProductId], [UserRating]) VALUES (6, 1, 5)
INSERT [dbo].[Ratings] ([AutoId], [ProductId], [UserRating]) VALUES (7, 2, 2.5)

And the output should be the following:

ProductId - ProductName - User Rating
1 - Denim - 5
2 - Denim 2 - 2
3 - Pringles - 0
4 - Pringles 2 - 2.5
5 - Pringles 3 - 0

This is the rating system of a project and I am trying to get the above output using Linq. With the following Sql, I got the result:

SELECT m.ProductId, m.ProductName, ISNULL(SUM(k.UserRating) / COUNT(k.ProductId), 0) AS 'User Rating' FROM Products m
LEFT JOIN Ratings k ON k.ProductId = m.ProductID
GROUP BY m.ProductID, m.ProductName

Unfortunately, with the following Linq, I get only the rating details that exist in the Ratings table:

var con = (from c in db.Ratings
           join d in db.Products on c.ProductId equals d.ProductID into ps
           from rt in ps.DefaultIfEmpty()
           group new { c, ps } by new { rt.ProductID, rt.ProductName } into g
           select new ProductRating
           {
              ProductId = g.Key.ProductID,
              ProductName = g.Key.ProductName,
              Total = g.Sum(c => c.c.UserRating) / g.Count()
           }).ToList();

Note: My goal is to get the rating details (sum of a product's rating) that don't exist in the Ratings table (Should return '0' if no data) along with the ratings details that exist.

Update 1 - Class ProductRating:

public class ProductRating {
     public int ProductId { get; set; }
     public string ProductName { get; set; }
     public double Total { get; set; } //Property for the rating
  }

Update 2 - Class Products and Ratings:

 public partial class Products
 {
     public int ProductID { get; set; }
     public string ProductName { get; set; }
     public Nullable<double> Price { get; set; }
     public string Details { get; set; }
     public Nullable<int> CategoryID { get; set; }
     public ICollection<Ratings> Rating { get; set; }
 }

 public partial class Ratings
 {
    public int AutoId { get; set; }
    public int ProductId { get; set; }
    public double UserRating { get; set; }
 }

Used the following the Linq query and got this error - The specified type member 'Rating' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported:

 var con = db.Products.Select(c => new ProductRating
           {
              ProductId = c.ProductID,
              ProductName = c.ProductName,
              Total = c.Rating.Average(d => (double?)d.UserRating) ?? 0
           }).ToList();

Update 3: Now getting this error - error 2016: The value specified for the condition is not compatible with the type of the member and this is how I tried for the navigation property:

Note: I've modified Andrés Robinet's query and it worked though using the Join.

Answer 1

from c in db.Ratings
join d in db.Products on c.ProductId equals d.ProductID into ps
from rt in ps.DefaultIfEmpty()

is the LINQ equivalent of the SQL Ratings LEFT OUTER JOIN Products, i.e. exactly the opposite of your SQL query.

While the LINQ query can be modified to match the SQL query, it doesn't make sense because EF provides a much better way which totally eliminates the need of using joins in the LINQ query - the so called navigation properties (see Don’t use Linq’s Join. Navigate!).

Normally the Product class would have a collection navigation property

public ICollection<Rating> Ratings { get; set; }

representing the one to many relationship between Product and Rating, and the equivalent LINQ query using it would be simply:

var result = db.Products
    .Select(p => new ProductRating
    {
        ProductId = p.ProductId,
        ProductName = p.ProductName,
        Total = p.Ratings.Average(r => (double?)r.UserRating) ?? 0
    }).ToList();

Update: In case you haven't set up correctly the relationship in the entity model, you could use the manual join equivalent of the above query by replacing the navigation property accessor with group join:

var result = (
    from p in db.Products
    join r in db.Ratings on p.ProductId equals r.ProductId into ratings
    select new ProductRating
    {
        ProductId = p.ProductId,
        ProductName = p.ProductName,
        Total = ratings.Average(r => (double?)r.UserRating) ?? 0
    }).ToList();

Answer 2

You have to start with the Product. Also, the order of your joins in your Linq expression is the opposite than in your SQL. And, the group ... by ... needs just rt.UserRating as the value selector

var con = (from d in db.Products
           join c in db.Ratings on d.ProductId equals c.ProductId into ps
           from rt in ps.DefaultIfEmpty()
           group new { rt.UserRating } by new { d.ProductId, d.ProductName } into g
           select new ProductRating
           {
               ProductId = g.Key.ProductId,
               ProductName = g.Key.ProductName,
               Total = g.Sum(r => r.UserRating) / g.Count()
           })
           .ToList();

Also, this would break if g.Count() is zero. But you have a few choices anyways:

• Add more complexity to the query and pray for EF to be able to translate it into SQL

  select new ProductRating
  {
      ProductName = g.Key.ProductName,
      Total = g.Count() > 0 ? (g.Sum(r => r.UserRating) / g.Count()) : 0
  }
  

• Redefine ProductRating or use a helper DTO, so that it takes Sum and Count properties (calculate the average on-demand, let's say, you can still have a Total property)

  select new ProductRating
  {
      ProductName = g.Key.ProductName,
      SumRating = g.Sum(r => r.UserRating),
      CountRating = g.Count()
  }   
  

Answer 3

Your LINQ statement has an inner join, whereas your SQL has a left join.