Избавиться от вложенного запроса
От: Suigintou  
Дата: 29.11.10 20:45
Оценка:
Из такого кода:
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

Что я делаю не так и как исправить? Спасибо за внимание.
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.