Из такого кода:
var pictures = db.GetTable<Picture>.AsQueryable();
if (query.StartDate != null)
pictures = pictures.Where(p => p.CreationDate >= query.StartDate);
if (query.EndDate != null)
pictures = pictures.Where(p => p.CreationDate <= query.EndDate);
//Возможно, тут можно сделать как-то более красиво, учитывая, что AcceptStatus - битовые флаги.
if (!query.AcceptStatus.HasFlag(AcceptStatus.Accepted))
pictures = pictures.Where(p => p.IsAccepted != true);
if (!query.AcceptStatus.HasFlag(AcceptStatus.Declined))
pictures = pictures.Where(p => p.IsAccepted != false);
if (!query.AcceptStatus.HasFlag(AcceptStatus.Pending))
pictures = pictures.Where(p => p.IsAccepted != null);
if (query.Tags != null && query.Tags.Length > 0)
pictures =
from picture in pictures
from tag in db.GetTable<Tag>.Where(tag => query.Tags.Contains(tag.Name))
from relation in db.GetTable<PictureTagRelation>()
where relation.PictureID == picture.ID && relation.TagID == tag.ID
select picture;
получается такой запрос:
SELECT
[relation].[ID] as [ID1],
[relation].[Hash] as [Hash1],
[relation].[Format] as [Format1],
[relation].[Width] as [Width1],
[relation].[Height] as [Height1],
[relation].[FileSize] as [FileSize1],
[relation].[ReplacementID] as [ReplacementID1],
[relation].[CreationDate] as [CreationDate1],
[relation].[CreatorID] as [CreatorID1],
[relation].[CreatorIP] as [CreatorIP1],
[relation].[IsAccepted] as [IsAccepted1]
FROM
(
SELECT
[p].[ID],
[tag].[ID] as [ID2],
[p].[CreationDate],
[p].[Hash],
[p].[Format],
[p].[Width],
[p].[Height],
[p].[FileSize],
[p].[ReplacementID],
[p].[CreatorID],
[p].[CreatorIP],
[p].[IsAccepted]
FROM
[Pictures] [p], [Tags] [tag]
WHERE
[p].[IsAccepted] <> 0 AND [p].[IsAccepted] IS NOT NULL AND
[tag].[Name] IN ('tag')
) [relation], [TagPictureRelations] [t1]
WHERE
[t1].[PictureID] = [relation].[ID] AND [t1].[TagID] = [relation].[ID2]
а хотелось бы:
SELECT
[p].[ID],
[p].[Hash],
[p].[Format],
[p].[Width],
[p].[Height],
[p].[FileSize],
[p].[ReplacementID],
[p].[CreationDate],
[p].[CreatorID],
[p].[CreatorIP],
[p].[IsAccepted]
FROM
[Pictures] [p], [Tags] [tag], [TagPictureRelations] [r]
WHERE
[p].[IsAccepted] <> 0 AND
[p].[IsAccepted] IS NOT NULL AND
[tag].[Name] IN ('tag') AND
[r].PictureID = [p].ID AND
[r].TagID = [tag].ID
Что я делаю не так и как исправить? Спасибо за внимание.