Select count from joined tables-CodePudding

I have two tables, CaseProductLinks and Products as shown here:

I am trying to get the following information using LINQ:

Here's what I would do in SQL:

SELECT 
    p.ProductID, p.ProductName, 
    COUNT(c.CaseID) AS Frequency 
FROM 
    CaseProductLinks c 
JOIN
    Products p ON c.ProductID = p.ProductID 
GROUP BY 
    p.ProductID

Here's what I have in C# so far which throws a "System.InvalidOperationException":

var objs = from p in _db.CaseProductLinks   
           join c in _db.Cases on p.ProductId equals c.ProductId into g
           select new S_Link
                      {
                           ProductID = p.ProductId,
                           ProductName = p.Product,
                           Frequency = g.Count() //Not sure what to do here
                      };

CodePudding user response：

If you've set your navigation up correctly (i.e. a Product has an ICollection<CaseProductLink> CaseProductLinks) you can simply do:

var r = _db.Products.Select(p =>
   new 
   {
       p.ProductId,
       p.ProductName,
       Frequency = p.CaseProductLinks.Count() 
   }
);

Here's what I would do in SQL:

If you're quite used to SQL it can be a step to pull yourself away from thinking in those ways and into the ways that EF is designed to abstract over them. One of the big plus points of EF is in telling it how your database tables/entities relate to each other, and then it will form the joins. It's not some dumb device that has to be pummeled into making every join and group that it does; if it knew there was 1 Product with Many CaseProductLink then it can write the join/group that counts the number of relates CPL per P simply by accessing the collection on Product with an operation that aggregates (Count)

If you don't have this nav set up, then I really would recommend to do so, as it's a big chunk of the beauty of EF that makes C# side code nice to work with

CodePudding user response：

I Test Below Code And Work Fine.Please check it

  public class Productlinks
        {
            public int  CaseId { get; set; }
            public int ProductId { get; set; }
        }

        public class Products
        {
            public int ProductId { get; set; }
            public string ProductName { get; set; }
        }

        static void Main(string[] args)
        {
            var products = new List<Products>()
            {
                new Products(){  ProductId=1, ProductName="A"},
                new Products(){  ProductId=2, ProductName="B"},
                new Products(){  ProductId=3, ProductName="C"},
            };
            var links = new List<Productlinks>()
            {
                new Productlinks(){ CaseId=1, ProductId=1 },
                new Productlinks(){ CaseId=3, ProductId=2 },
                new Productlinks(){ CaseId=3, ProductId=2 },
                new Productlinks(){ CaseId=4, ProductId=3 },
            };

            var objs = from p in products
                       join c in links on p.ProductId equals c.ProductId into g
                       select new 
                       {
                           ProductID = p.ProductId,
                           ProductName = p.ProductName,
                           Frequency = g.Count() 
                       };
        }