How to translate t-sql sum() to linq sum()

Question

I have something like this:

And I need to re-write this query into LINQ.

I tried:

var EachProduct = (from b in _context.ReleasePlans
                                    join a in _context.Products
                                        on b.ProductProductId equals a.ProductId
                                    where b.DepartmentDepartmentId == departmentId
                                     select new { ProductId = b, ProductName = a });

But how can I do SUM() and DATEPART() function in Linq?

UPD: Query using lambda expression. But the question remains the same

var EachProduct = _context.ReleasePlans
            .Where(b => b.DepartmentDepartmentId == departmentId)
            .Join(_context.Products, a => a.ProductProductId, b => b.ProductId,
                (b, a) => new {ProductId = b, ProductName = a});

For the quarter, see [How to get Current Quarter from Current Date using C#](http://stackoverflow.com/questions/21266857/how-to-get-current-quarter-from-current-date-using-c-sharp), and for the Sum you will need to use a linq group by, just like in the sql. — stuartd, Mar 03 '16 at 12:26
Switching to a lambada expression would be less clean but easier, wouldn't it? — misha130, Mar 03 '16 at 12:51
Yeah... this kind of LinqToSql-ism was what made me jump the ship over to PetaPoco... — code4life, Mar 03 '16 at 13:08
So the Sum(a=> a.Amount) will give you a sum and and Where(d => d.DateTime >= DateTime.Now.AddMonths(-3)) will give you a the last quarter. Don't forget to add a select in the end of it to tune the query. I'd write an answer but its really confusing when I dont have the objects on hand — misha130, Mar 03 '16 at 13:25

score 1 · Accepted Answer · answered Mar 03 '16 at 13:52

class Program
{
    public class ReleasePlan
    {
        public int ProductProductId { get; set; }
        public int Amount { get; set; }
        public DateTime DateTime { get; set; }
    }

    public class Product
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public int DepartmentDepartmentId { get; set; }
    }

    static void Main()
    {
        var products = new List<Product>
        {
            new Product {ProductId = 1, ProductName = "1", DepartmentDepartmentId = 1},
            new Product {ProductId = 2, ProductName = "2", DepartmentDepartmentId = 1},
            new Product {ProductId = 3, ProductName = "3", DepartmentDepartmentId = 1},
        };
        var releasePlans = new List<ReleasePlan>
        {
            new ReleasePlan {ProductProductId = 1, Amount = 1, DateTime = DateTime.Now},
            new ReleasePlan {ProductProductId = 1, Amount = 1, DateTime = DateTime.Now},
            new ReleasePlan {ProductProductId = 1, Amount = 1, DateTime = DateTime.Now.AddMonths(-5)},

            new ReleasePlan {ProductProductId = 2, Amount = 2, DateTime = DateTime.Now},
            new ReleasePlan {ProductProductId = 2, Amount = 2, DateTime = DateTime.Now},
            new ReleasePlan {ProductProductId = 2, Amount = 2, DateTime = DateTime.Now.AddMonths(-5)},

            new ReleasePlan {ProductProductId = 3, Amount = 3, DateTime = DateTime.Now},
            new ReleasePlan {ProductProductId = 3, Amount = 3, DateTime = DateTime.Now},
            new ReleasePlan {ProductProductId = 3, Amount = 3, DateTime = DateTime.Now.AddMonths(-5)},

        };


        var amountByProducts = from rp in releasePlans
            join p in products
                on rp.ProductProductId equals p.ProductId
            where p.DepartmentDepartmentId == 1 && (rp.DateTime.AddDays(2).Month/3) == 1
            group new {rp, p} by new {rp.ProductProductId, p.ProductId, p.ProductName}
            into grp
            select new
            {
                grp.Key.ProductId,
                grp.Key.ProductName,
                PlannedAmount = grp.Sum(g => g.rp.Amount)
            };

        Console.WriteLine("ProductId    ProductName     PlannedAmount");
        foreach (var producAmount in amountByProducts)
        {
            Console.WriteLine("{0} \t\t{1} \t\t{2}", producAmount.ProductId, producAmount.ProductName,
                producAmount.PlannedAmount);
        }
    }
}

How to translate t-sql sum() to linq sum()

1 Answers1