CODEWITHSUNDEEP
c#linqlinq-to-sql
user2275330
user2275330

Reputation: 3

Sql to Linq Query Issue

Hi I am trying to get this sql query to linq and I am having issues because of the join.

sql:

SELECT 
//fields here
FROM CustomFields cf LEFT JOIN CustomValues cv on cv.CustomFieldID = cf.CustomFieldID AND cv.RefID=362373
WHERE cf.OrganizationID = 1078
AND cf.RefType=9
ORDER BY cf.Position

the linq I have is:

from cf in CustomFields
join cv in CustomValues on cf.CustomFieldID equals cv.CustomFieldID
where cf.OrganizationID == 1078 && cf.RefType == 9 && cv.RefID == 362373
select cf

Upvotes: 0

Views: 68

Answers (1)

Todd
Todd

Reputation: 1081

var query = from cf in CustomFields
join cv in CustomValues on cf.CustomFieldID equals cv.CustomFieldID into r
from cv in r.Where(cv => cv.RefID == 362373).DefaultIfEmpty()
where cf.OrganizationID == 1078 && cf.RefType == 9
select cf

And here is some explanation:

The join clause compares the specified keys for equality by using the special equals keyword. A join clause with an into expression is called a group join.into keyword to creates an identifier. If no elements from the right source sequence are found to match an element in the left source, the join clause will produce an empty array for that item. Therefore, the group join is still basically an inner-equijoin except that the result sequence is organized into groups. To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. The where clause is a filtering mechanism. It can be positioned almost anywhere in a query expression, except it cannot be the first or last clause. A where clause may appear either before or after a group clause depending on whether you have to filter the source elements before or after they are grouped.

source: Microsoft

Upvotes: 2

Related Questions