Sunday, November 8, 2009

Linq to SQL First Cut

I experimented a little bit with Linq to SQL and learned some of the basic syntax.  Here is a screenshot of the DBML designer file:

Capture

Here are two LINQ queries I developed with their ANSI SQL equivalents:

SELECT Newspapers.*
FROM Newspapers
INNER JOIN Newspapers_Advertisements
ON Newspapers.Newspaper_Id = Newspapers_Advertisements.Newspaper_Id
WHERE Newspapers_Advertisements.Advertisement_Id = '@guid'

List<Newspaper> allPapers = new List<Newspaper>();
allPapers.AddRange(
    from paper in db.Newspapers
    join paper_ad in db.Newspapers_Advertisements
        on paper.Newspaper_Id equals paper_ad.Newspaper_Id
     where paper_ad.Advertisement_Id == adId
     select paper
);

SELECT DISTINCT Newspapers.*
FROM Newspapers
WHERE Newspapers.Newspaper_ID NOT IN
(SELECT Newspapers_Advertisements.Newspaper_Id
FROM Newspapers_Advertisements
WHERE Newspapers_Advertisements.Advertisements_Id = '@guid')

List<Newspaper> allPapers = new List<Newspaper>();
allPapers.AddRange(
    (from paper in db.Newspapers
     where !db.Newspapers_Advertisements.Any(
         na => (na.Newspaper_Id == paper.Newspaper_Id
         & na.Advertisement_Id == adId))
     select paper).Distinct()
);

No comments:

Post a Comment