Are there any linq experts that can help a guy out?
This is the SQL I need to convert to LINQ
select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[Customer].[Name] as [Customer_Name],
[CustCnt].[Name] as [CustCnt_Name],
from Erp.OrderHed as OrderHed
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
inner join Erp.CustCnt as CustCnt on
Customer.Company = CustCnt.Company
and Customer.CustNum = CustCnt.CustNum and ( CustCnt.ConNum = OrderHed.PrcConNum )
The last line is the one I can figure out how to translate.
I am comparing a field in the linked tabled(CustCnt) with a field in an already linked table(OrderHed)… Any help would be greatly appreciated. Thanks for reading…
For reference this is what I have so far…
var soahead = ( from a in Db.OrderHed
join b in Db.Customer on
new { a.Company , a.CustNum } equals
new { b.Company , b.CustNum }
join c in Db.CustCnt on
new { a.Company , a.CustNum} equals
new { c.Company , c.CustNum}
where a.OrderNum == OrdNum
select new {a.OrderNum});
You’ve pretty much nailed it, just add a third field to your join objects:
var soahead = ( from a in Db.OrderHed
join b in Db.Customer on
new { a.Company , a.CustNum } equals
new { b.Company , b.CustNum }
join c in Db.CustCnt on
new { a.Company , a.CustNum, a.PrcConNum } equals // Just add another field here
new { c.Company , c.CustNum, c.ConNum } // and here
where a.OrderNum == OrdNum
select new { a.OrderNum } );
I tried that …
join c in Db.CustCnt on
new { a.Company , a.CustNum,a.PrcConNum} equals
new { c.Company , c.CustNum,c.ConNum}
and received error:
CS1941 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to ‘Join’.
Chapgpt said one solution to that error is to use DefaultIfEmpty()
So I have tried this:
join c in Db.CustCnt on
new { a.Company , a.CustNum,p1=a.PrcConNum} equals
new { c.Company , c.CustNum,p1=c.ConNum} into prcust
from c in prcust.DefaultIfEmpty()
It compiled, but not sure of results yet. Have a lot more coding before I see results.
Also, can I ask what you are ultimately trying to get? You are returning the order number, but you fed that in so you have that already. What are you trying to learn?
Yeah that was just to show a select statement to make it easier to read.
What I am retrieving is the Primary Contact(OrderHed.PrcConNum) for the customer from CustCnt table. I also will be retrieving the Ship To Contact(OrderHed.ShpConNum) from the same table.
If you had inner joins and the sql worked then just assigning the two mismatched variables to the common name p1 will clear the error.
The into makes it an outer join that could return nulls that you will have to handle.
