CODEWITHSUNDEEP
sqlasp.net-mvc-3linq
ɐsɹǝʌ ǝɔıʌ
ɐsɹǝʌ ǝɔıʌ

Reputation: 4512

Complex SQL to LINQ in C#

I'm completely newbie in LINQ and Lamda expressions. I have a bit complex SQL statement that retrieves information from several database tables:

SELECT A.OrderID, A.FORMJDENO, A.Title, B.Descr, C.Code, C.Descr, D.ModificationDate from OrderForm A 
LEFT JOIN OrderPriority B ON A.OrderPriorityID = B.OrderPriorityID 
LEFT JOIN StockClass C ON A.StockClassID = C.StockClassID 
LEFT JOIN AuditTrailLog D ON A.OrderID = D.ObjectID 
WHERE D.ColumnInfoID= 487 AND D.OldValue='1' AND D.NewValue='2' AND A.FormStatus=2 AND A.FormType=3 AND B.OrderPriorityID=1000001 AND C.StockClassID=1000002
AND A.DeptID IN 
    ( SELECT DeptID FROM Department WHERE InstID = 1000006 )
AND DATEDIFF(m,D.ModificationDate, A.VendorDeliveryDate) >= 3

I already have the LINQ almost done, using .Contains() method replacing the WHERE...IN SQL clause, but i need some help to make de joins restricting the results basing on values that belongs to other tables and using DATEDIFF equivalent in LINQ. This is what I already have got and is working fine, but obviously it is no restricting the results like the above SQL statement. I tried several ways with no success. I need the equivalent LINQ

UPDATE:

Finally I was able to get it working. This is the final LINQ that retrieves the same records than the SQL statement. Many thanks to @Gert Arnold:

var valid = dba.OrderForm
            .Where(q => q.FormType == 3
                        && q.FormStatus == 2
                        && q.OrderPriority.OrderPriorityID == orderpriorityID
                        && q.StockClass.StockClassID == stockclassID
                        && dba.AuditTrailLog.Where(log => q.OrderID==log.ObjectID)
                                .Any(log => log.ColumnInfoID == 487
                                            &&  log.OldValue == "1"
                                            && log.NewValue == "2"
                                            && EntityFunctions.DiffMonths(log.ModificationDate,     
                                                                 q.VendorDeliveryDate) >= period)
                        && departments.Contains(q.DeptID));

Upvotes: 1

Views: 452

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109079

I think this is about it:

dba.OrderForm.Where(q => q.FormType == 3 
    && q.FormStatus == 2   
    && q.OrderPriority.OrderPriorityID == 1000001
    && q.StockClass.StockClassID == 1000002
    && q.AuditTrailLogs
        .Any(log => log.ColumnInfoID == 487
                 && log.OldValue == "1"
                 && log.NewValue == "2"
                 && EntityFunctions.DiffMonths(log.ModificationDate, 
                                                 q.VendorDeliveryDate) >= 3)
    && departments.Contains(q.DeptID));

I assume you've got (or can create) a navigation property OrderTable.AuditTrailLogs.

Without navigation property you can use

&& dba.AuditTrailLogs.Where(log => q.OrderID == log.ObjectID).Any(...

Upvotes: 3

Related Questions