(complex?) LINQtoSQL query

I have two tables: Designs and Urls. A Design can be related to many URLs through a Guid (which is not the primary key of the Design table, as a URL can be related to other tables as well through the same column).

Design (table)
-------
DesignGuid

URL (table)
------
RelationalGuid
UrlString

In my code I am trying to determine if some information that I am given requires the creation of a new Design record, or if it corresponds to an existing Design record. The information includes a list of URLs. These URLs are the only way to determine whether or not a new record needs to be created or not.

var localUrlStrings = new List<string>() { "urlString1", "urlString2" }

So, I need to take a list of URLs th开发者_如何学JAVAat I am given and write a query that looks at existing Design records and their associated URLs and return a match IFF the URLs match exactly. So, if I'm given 2 URLs in my code, I need to find only those Designs (should be either 1 or 0 matches) that are associated to exactly 2 URLs that match the 2 I have in code exactly.

Thanks, Jeromeyers

---

If I understand your problem correctly, I think you want a SQL query that looks something like this:

SELECT d.DesignGuid
FROM Design AS d
INNER JOIN URL as u ON d.DesignGuid = u.RelationalGuid
WHERE u.UrlString IN ('urlstring1', 'urlstring2', ... ,'urlstringn')

I think something like this should work:

var localUrlStrings = new List<string>() { "urlString1", "urlString2" }

var result = 
    from d in Design
    from u in URL
    where d.DesignGuid == u.RelationalGuid
    && localUrlStrings.Contains(u.UrlString)
    select new {
        d.DesignGuid,
        u.UrlString
    });

Edit: You're looking for this:

SELECT d.DesignGuid, count(u.URL) as numURLs
FROM Design as d,
     Url as u
WHERE d.DesignGuid = u.RelationalGuid
HAVING count(u.URL) = 2

I can't test it right now, but I believe this should do the trick (the syntax might be a little off, I'll double check it in the morning):

var result = 
    from d in dataContext.Designs
    join u in dataContext.URLs on d.DesignGuid equals u.RelationalGuid into joinTable
    from j in joinTable
    where localUrlStrings.Contains(j.UrlString)
    group j by j.RelationalGuid into groupTable
    where groupTable.Count() == localUrlStrings.Count
    select groupTable.Key;

---

try (not entirely sure about the exact result needed):

var Result = from d in Design from u in URL from l in localUrlStrings 
             where d.DesignGuid == u.RelationalGuid and 
             l == u.UrlString select new { l, d.DesignGuid };