Hi I am trying to get this sql query to linq and I am having issues because of the join.
FROM CustomFields cf LEFT JOIN CustomValues cv on cv.CustomFieldID = cf.CustomFieldID AND cv.RefID=362373
WHERE cf.OrganizationID = 1078
ORDER BY cf.Position
from cf in CustomFields
join cv in CustomValues on cf.CustomFieldID equals cv.CustomFieldID
where cf.OrganizationID == 1078 && cf.RefType == 9 && cv.RefID == 362373
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:
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.