Click here to Skip to main content
14,243,559 members
Rate this:
Please Sign up or sign in to vote.
hello i need to translate the following sql to linq to entites c# iam using EF 6


SELECT  ProductID, BillType, SUM(PermitQuantity) AS quantity,  BillDate  = DATEADD(MONTH, DATEDIFF(MONTH, 0, BillDate), 0)
 FROM  ViewRpt_ProductsTrans
 WHERE BillType IN (2,3)
 AND  ProductCode='79'
 AND BillIsPermitted= 1
 GROUP BY  DATEADD(MONTH, DATEDIFF(MONTH, 0, BillDate), 0),ProductID,BillType
 ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, BillDate), 0)


iam unable to translate the following part
DATEADD(MONTH, DATEDIFF(MONTH, 0, BillDate), 0)


What I have tried:

var trans = (from p in _context.ViewRpt_ProductsTrans
                        let groupDate = new DateTime(p.BillDate.Value.Year, p.BillDate.Value.Month, 0, 0, 0, 0)
                        where
                                p.ProductID == productId &&
                                p.BillIsPermitted == true &&
                                (p.BillType == 3 || p.BillType == 2)
                        group p by
                        new
                        {
                            p.ProductID,
                            p.BranchID,
                            p.BillType,
                            groupDate
                        } into g

                        select new BranchesProductTransViewModel()
                        {
                            BillDate = g.Key.groupDate,
                            BillType = g.Key.BillType.Value,
                            BranchId = g.Key.BranchID.Value,
                            ProductId = g.Key.ProductID,
                            Quantity = g.Sum(x => x.PermitQuantity).GetValueOrDefault(0)
                        });
Posted
Updated 5-Sep-17 6:13am
Comments
Thanks7872 5-Sep-17 9:31am
   
Have a look at this : https://stackoverflow.com/a/11665220

Basically it is used to find start of the month in which the bill date is.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Something like this should work:
let groupDate = DbFunctions.AddMonths(new DateTime(1753, 1, 1), EntityFunctions.DiffMonths(new DateTime(1753, 1, 1), p.BillDate))
(Using the namespace System.Data.Entity)

Or:
let groupDate = SqlFunctions.DateAdd("month", SqlFunctions.DateDiff("month", new DateTime(1753, 1, 1), p.BillDate), new DateTime(1753, 1, 1))
(Using the namespace System.Data.Entity.SqlServer)

The main difference between the two is that SqlFunctions only works for SQL Server, whereas EntityFunctions should work for other providers as well.

NB: 1st January 1753 is the earliest date supported by SQL's datetime type. If you're using SQL 2008 or later, your columns should be defined as datetime2, in which case you could use DateTime.MinValue instead.
   
v2
Comments
TheSniper105 6-Sep-17 1:42am
   
thank you very much worked for me i go for first solution as i use different provider
but only one note replace EntityFunctions with DbFunctions as the first is obsolete now
Richard Deeming 6-Sep-17 11:20am
   
Thanks for the update - I hadn't spotted the Obsolete attribute.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100