Linq 2 SQL Syntax Help

Question

I am almost done converting a MySQL app to Linq2SQL but struggling to get my head round this last SQL query

SELECT a.URL, b.URL AS DuplicateURL
FROM Pages a
INNER JOIN Pages b ON a.MetaDescription = b.MetaDescription
                   AND a.PageID <> b.PageID
WHERE a.DomainID = @reportid
  AND b.DomainID = @reportid
  AND LENGTH(TRIM(a.MetaDescription)) > 0
  AND a.URL < b.URL
ORDER BY a.URL ASC

Could anymore give me some syntax help on how I would create this query in Linq2SQL?

Any help greatly appreciated

Answer 1

That's pretty straigtforward:

var reportId = ...;

var duplicates =
    from a in db.Pages
    from b in db.Pages
    where a.MetaDescription == b.MetaDescription
    where a.PageID != b.PageID
    where a.DomainID == reportId
    where b.DomainID == reportId
    where a.MetaDescription.Trim().Length > 0
    where a.URL < b.URL
    orderby a.URL
    select new { Url = a.URL, DuplicateUrl = b.Url }

Answer 2

It goes like this:

var DuplicatePages = 
    from a in DBContext.Pages
    join b in DBContext.Pages on a.MetaDescription equals b.MetaDescription
    where (a.PageID <> b.PageID) && (a.DomainID == ReportID) && 
    (b.DomainID == ReportID) && (a.MetaDescription.Trim().Length > 0) && 
    (a.URL < b.URL)
    orderby a.URL
    select new { Url = a.URL, DuplicateUrl = b.URL };