Click here to Skip to main content
15,879,326 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hello i need to translate the following sql to linq to entites c# iam using EF 6


SQL
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:

C#
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

Something like this should work:
C#
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:
C#
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.
 
Share this answer
 
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900