(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 };
精彩评论