Linq Correlated query // Contains

Question

Edited

Solution using or rather than union ! It seems there is a problem with linq2sql w.r.t selecting a relation post a UNION msdn's social forums also indicate it as so

---

Intent To get WCtegories & WPBands having items ("W") and each item with either a price or an offer specified

Sql just works fine but linq is bugging out with errors The column prefix 't2' does not match with a table name or alias name used in the query. The inner query can not access outer query's variable.

Could some help me out with linq on how exactly to achieve this ?

Sql

SELECT  *
FROM    [WCategories] AS a
        JOIN [WPbands] AS b ON ( a.[CategoryId] = b.[WCategoryId] )
        JOIN [W] AS c ON ( a.[CategoryId] = c.[CategoryId] )
WHERE   a.[Visible] = 1 AND b.[Visible] = 1 AND c.[Visible] = 1
        AND c.[WineId] IN        
        (
            SELECT [WId] FROM [VWPrices] 
            WHERE [WId] = c.[WId] AND 
            [Price] BETWEEN b.[Floor] AND b.[Ceiling]

            UNION

         SELECT [WId] FROM [VWOffers]
         WHERE [WId] = c.[WId] AND 
         [Price] BETWEEN b.[Floor] AND b.[Ceiling] AND
         GETDATE() BETWEEN [StartDt] AND [EndDt]
        )

Linq

    from a in _db.WCategories
join b in _db.WPbands on a.CategoryId equals b.WCategoryId 
join c in _db.W on a.CategoryId equals c.CategoryId
where
  a.Visible &&   b.Visible && c.Visible &&
    (
     (
      from t in _db.VWPrices
      where
        t.WId == c.WId &&
        t.Price >= b.Floor && t.Price <= b.Ceiling
      select t.WId
     ).Union
     (
      from s in _db.VWOffers
      where
        s.WId == c.WId &&
        s.Price >= b.Floor && s.Price <= b.Ceiling &&
        DateTime.Now >= s.StartDt && DateTime.Now <= s.EndDt
      select s.WId
     )
    ).Contains(c.WId)
select new {a.Name,  b.Tag}

Sql being generated by Linq is as follows

     EXISTS ( 
      SELECT NULL AS [EMPTY]
                FROM ( 
                         SELECT    [t5].[WId]
                             FROM      (....) AS [t5]                                        
                         ) AS [t6]
            WHERE    [t6].[WId] = [t2].[WId] ) )

could some one pls explain why "Select NULL AS [Empty]" is being added during generation ? if i were to delete t6 everything is fine with the generated sql

Answer 1

This looks very similar to this question:

http://stackoverflow.com/questions/1362609/combine-join-with-string-contains-in-linq-query