Linq: Combining Join and Group By

LINQ: combining join and group by

Once you've done this

group p by p.SomeId into pg  

you no longer have access to the range variables used in the initial from. That is, you can no longer talk about p or bp, you can only talk about pg.

Now, pg is a group and so contains more than one product. All the products in a given pg group have the same SomeId (since that's what you grouped by), but I don't know if that means they all have the same BaseProductId.

To get a base product name, you have to pick a particular product in the pg group (As you are doing with SomeId and CountryCode), and then join to BaseProducts.

var result = from p in Products                         
 group p by p.SomeId into pg                         
 // join *after* group
 join bp in BaseProducts on pg.FirstOrDefault().BaseProductId equals bp.Id         
 select new ProductPriceMinMax { 
       SomeId = pg.FirstOrDefault().SomeId, 
       CountryCode = pg.FirstOrDefault().CountryCode, 
       MinPrice = pg.Min(m => m.Price), 
       MaxPrice = pg.Max(m => m.Price),
       BaseProductName = bp.Name  // now there is a 'bp' in scope
 };

That said, this looks pretty unusual and I think you should step back and consider what you are actually trying to retrieve.

LINQ to SQL with multiple joins, group by and count

1. For Left Joins - I suggest using a from with a where and a DefaultIfEmpty

2. You need to group using an anonymous type in order to group multiple parameters

Value cannot be null. Parameter name: inner

Are any of joining properties nullable?

 var qry = 
        from Cust in tblCust
        from Order in tblOrder.Where(x => Cust.CustID == x.CustID)
                              .DefaultIfEmpty()
        from Item in tblItem.Where(x => Order.OrderID == x.OrderID)
                            .DefaultIfEmpty()
        group new { Cust, Order.OrderId, Item.ItemId } by new { Cust.CustID, Cust.CustName } into grp
        let numItems = grp.Select(x => x.ItemId).Distinct().Count()
        orderby numItems 
        select new
        {
            ID = grp.Key.CustID,
            Name = grp.Key.CustName,
            numOrders = grp.Select(x => x.OrderId).Distinct().Count(),
            numItems,
        };

LINQ multiple Joins with a Group By

You probably need to assign a name to the anonymous type properties in the grouping due to multiple properties with the same name. (two times the EmployeeId?)

    var query = (
        //Customers
        from customer in context.Customers

        //Engineer 1
        join engineer1 in context.Employees on customer.PrimaryEngineer equals
        engineer1.EmployeeId into eng1 
        from engineer1 in eng1.DefaultIfEmpty()

        //Engineer Top
        join engineerTop in context.Employees on customer.TopEngineer equals 
        engineerTop.EmployeeId into top
        from engineerTop in top.DefaultIfEmpty()
        group new {customer, engineer1, engineerTop} by new {CustName = customer.Name, EmpId1 = engineer1.EmployeeId, EmpId2 = engineerTop.EmployeeId} into grp
        select new
        {
            Name = grp.Key.CustName,
            EmpId1 = grp.Key.EmpId1,
            EmpId2 = grp.Key.EmpId2
        });

LINQ multiple joins with a Group By not Working

Try this one your controller action. you will get site id and orders for site

public ActionResult draw_chart(string city)
                {
                    var query = from citiez in db.cities
                                join site in db.sites on citiez.city_id equals site.city_id
                                join ords in db.orders on site.site_id equals ords.site_id
                                where  citiez.city_name == city                            
                                group site by site.site_id into grouped
                                select new
                                {

                                    siteId = grouped.Key,
                                    ordersforsite = grouped.Count(),
                                };
                    var list = query.ToList();
                    return Json(list, JsonRequestBehavior.AllowGet);
                }

Linq query with multiple joins and groups

Not sure how EF Core supports Count with condition, so just emulating with Sum.

var query =
   from p in Persons
   join a in Addresses on p.PersonId equals a.PersonId into addressGroup 
   from a in addressGroup.DefaultIfEmpty()
   join c in Contracts on p.PersonId equals c.PersonId into contractGroups 
   from c in contractGroups.DefaultIfEmpty()
   join cv in ContractVersions on c.ContractId equals cv.ContractId in versionsGroup
   from cv in versionsGroup.DefaultIfEmpty()
   group new { a, cv } by new { p.Name,  c.ContractType } into g
   select new 
   {
       g.Key.Name, 
       NumberOfAddresses = g.Sum(x => x.a.Street != null ? 1 : 0), 
       g.Key.ContractType, 
       Number = g.Sum(x => x.cv.ContractVersionId != null ? 1 : 0), 
       LastChangeDate = g.Max(x => x.cv.ChangedDate)
   }

Multiple join with groupby in linq

group <--> by <--> into <--> is changed your scope to IGrouping<a,b>

My opinion is not only apt.Gender is your key but city.CityName and prg.Program

try this (or some similar):

group apt by new{apt.Gender, city, prg} into grouped
select new CityWiseStudentModel
{
     CityName = grouped.Key.city.CityName, 
     Gender = grouped.Count(), //rename GenderCount
     programName = grouped.Key.prg.Program,
     // Gender = grouped.Key.Gender,
} 

How to combine LEFT JOIN, GROUP BY and SUM in LINQ?

I ended up using Navigation Properties suggested by Gert Arnold. Here is simplified version of my problem and its solution:

Models:

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Income
{
    public int Id { get; set; }
    public int OrganizationId { get; set; }
    public decimal Amount { get; set; }
}

public class Organization
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int AnalystId { get; set; }
    public virtual Employee Analyst { get; set; }

    public int DirectorId { get; set; }
    public virtual Employee Director { get; set; }

    public virtual ICollection<Income> Incomes { get; set; }
}

Query:

class Program
{
    static void Main(string[] args)
    {
        var context = new Context();

        var queryResult = context.Organizations.Select(x => new
        {
            x.Id,
            x.Name,
            Director = x.Director.FirstName + " " + x.Director.LastName,
            Analyst = x.Analyst.FirstName + " " + x.Analyst.LastName,
            TotalIncome = x.Incomes.Sum(y => y.Amount)
        });

    }
}

How to get Min in C# Linq with join and group by?

You will need to adjust your group to return the t1 record since you want to access its ProductPrice property:

group t1 by new { t1.ProductId, t2.ProductName } into topPrices

Then all you need is to perform the Min on the topPrices group, like this:

TheCheapestPrice = topPrices.Min(t => t.ProductPrice)

Link to working fiddle.

HTH

Linq Join with GroupBy and Sum

Try this.

await _context.SalesTransactions
        .GroupBy(w => w.CustomerId)
        .Select(x => new SalesTransactions() 
        {
            CustomerId = x.Key,
            Amount = x.Sum(w => w.Amount)
        }).ToListAsync();

EDITED 2

await _context.SalesTransactions.Include(x => x.Customer).ToListAsync()
    .GroupBy(w => new { w.CustomerId, w.Customer })
    .Select(x => new SalesTransactions() 
    {
        CustomerId = x.Key.CustomerId,
        Customer = x.Key.Customer,
        Amount = x.Sum(w => w.Amount)
    }).ToListAsync();

Can get name from Customer.Name in SalesTransaction class.

---

Related Topics