How can I Write This SQL Query to Linq Query

Question

    SELECT DISTINCT SOM.CompanyID,
SOM.BranchID,
SOM.SOID,
SOM.TranDocNo,
SOM.DocDate,
CM.CustomerName,
STM.SaleTypeName,
SOM.NetAmount,
SOM.TotQty
FROM tblSODetails SOD WITH (NOLOCK)
INNER JOIN tblSOMaster SOM WITH (NOLOCK) ON (SOD.SOID = SOM.SOID)
INNER JOIN tblSaleTypeMaster STM WITH (NOLOCK) ON (SOM.SaleTypeID = STM.SaleTypeID)
INNER JOIN tblCustomerMaster CM WITH (NOLOCK) on (SOM.CustomerID = CM.CustomerID)
LEFT OUTER JOIN tblSOReturnDetails SORD WITH (NOLOCK) ON (SOD.SODetID = SORD.SODetID)
LEFT OUTER JOIN tblSOReturnMaster SORM WITH (NOLOCK) ON (SORD.SOReturnID = SORM.SOReturnID)
LEFT OUTER JOIN tblOutwardMaster OM WITH (NOLOCK) ON (SOM.TranDocNo = OM.RefTranDocNo)
LEFT OUTER JOIN tblOutwardDetails OD WITH (NOLOCK) ON (OM.OutwardID = OD.OutwardID AND OD.ItemID = SOD.ItemID)
WHERE SOM.CompanyID = 1 
and SOM.BranchID = 1 
and SOM.IsOutward = 0 
and SOM.DocTypeID = 3 
and SOM.IsSOReturn = 0 
and SOM.IsDealer = 0
and STM.IsStockEffect = 1 
and STM.IsSaleOrder = 1 
and SOM.Status = 'AP' 
and CM.IsBlackListed = 0
AND SOD.Qty > (ISNULL(OD.Qty,0) + ISNULL(SORD.Qty,0))

How To Write This SQL Query in the Form Of LINQ Query.. AND SpeciFically Last Line Of Query.To Write In LINQ Query

I Write this query to resolve my problem.

(from SOD in db.tblSODetails
                              join SOM in db.tblSOMasters on SOD.SOID equals SOM.SOID
                              join STM in db.tblSaleTypeMasters on SOM.SaleTypeID equals STM.SaleTypeID

SOM.CustomerID equals CM.CustomerID join SORD in db.tblSOReturnDetails on SOD.SODetID equals SORD.SODetID into SORD_join

                              from SORD in SORD_join.DefaultIfEmpty()



                              from CM in db.tblCustomerMasters.Where(
                                 CM => CM.CustomerID == SOM.CustomerID && (CM.IsBlackListed == false))
                              SOD.SODetID).DefaultIfEmpty()

                              join SORM in db.tblSOReturnMasters on SORD.SOReturnID equals SORM.SOReturnID into SORM_join

                              from SORM in SORM_join.DefaultIfEmpty()

                              join OM in db.tblOutwardMasters on new { TranDocNo = SOM.TranDocNo } equals
                              new { TranDocNo = OM.RefTranDocNo } into OM_join

                              from OM in OM_join.DefaultIfEmpty()

                              join OD in db.tblOutwardDetails on new { OM.OutwardID, SOD.ItemID } equals
                               new { OD.OutwardID, OD.ItemID } into OD_join

                              from OD in OD_join.DefaultIfEmpty()

                              where (SOM.CompanyID == compID)
                           && (SOM.BranchID == BranchID)
                           && ((isOutward == -1) || (SOM.IsOutward == (isOutward == 1 ? true : false)))
                           && (SOM.DocTypeID == 3)
                           && (SOM.Status == "AP")
                           && (SOM.IsSOReturn == false)
                           && (SOM.IsDealer == false)
                           && (STM.IsStockEffect == true)
                           && (STM.IsSaleOrder == true)
                           && ((SOM.TranDocNo.Contains(strDocTypeCode)))
                           && SOD.Qty > (((System.Decimal?)(OM.TotQty) ?? (System.Decimal?)0) + ((System.Decimal?)(SORM.TotQty) ?? (System.Decimal?)0))

                              select new
                              {
                                  SOM.CompanyID,
                                  SOM.BranchID,
                                  SOM.SOID,
                                  TranDocNo = SOM.TranDocNo,
                                  DocDate = SOM.DocDate,
                                  CustomerName = CM.CustomerName == null ? "" : CM.CustomerName,
                                  DealerName = "",
                                  STM.SaleTypeName,
                                  NetAmount = SOM.NetAmount,
                                  TotQty = SOM.TotQty
                              }
                             ).Distinct();

Answer 1

there's fluent and query expression styles in LINQ if you want your project data accessing to be using LINQ. Then, you need to start writing query starting with one of the LINQ styles. I don't recomment to write sql query and then convert it, you will waste a lot of time doing that. Once you good and feeling familiar with LINQ, you will find it's much easier to start querying using LINQ instead of converting.

Using Query expression is easier for begineer in JOINING tables, while for just retriving data from one table Fluent style is much easier.

so ill start with query style

from thing1 in table1 join thing2 in table2 on thing1.Id equals thing2.Thing1Id
 select new // anonymous type
 {
 Thing1Column= thing1.SomeColumn,
 Thing2Comumn= thing2.SomeColumn,
 BlaBlaBla= thingX.blablabla
 };

or you can use the fluent style

The Join operator takes a number of parameters:

• IEnumerable inner – the inner sequence.
• Func outerKeySelector – what key to join on in outer sequence elements.
• Func innerKeySelector - what key to join on in inner sequence elements.
• Func resultSelector – What the output elements will look like

Table1.Join(Table2, (Thing1 outerKey) => outerKey.Id, (Thing2 innerKey) => innerKey.Thing1Id, (thing1, thing2) => thing1.AnyColumn + " - " + thing2.AnyOtherColumn);

Thing1 is class (Model) thing1 is an object from that class

that's for inner for left outer then you need DefaultIfEmpty() which gives you the result that have no matching elements in the second table

from thing1 in table1 join thing2 in table2 on thing1.Id equals thing2.Thing1Id into thingies2Group from x in reviewGroup.DefaultIfEmpty(new Thing2{MayBeEmptyValuesColumn= "DefaultValueIfEmpty"})
 select new // anonymous type
 {
 Thing1Column= thing1.SomeColumn,
 Thing2Comumn= x.MayBeEmptyValuesColumn,
 BlaBlaBla= thingX.blablabla
 };