SQL Linq Question

Question

I have the following working TSQL query in ms SQL 2008

SELECT
    Date,
    COUNT(click) AS clicks,
    COUNT(sale) AS sales,
    count(lead) as leads
FROM
(
SELECT ClickDate as date ,ID AS click ,CAST(NULL AS int) AS  sale , CAST(null as int) as lead
FROM clicks

UNION ALL

SELECT Date,null, ID ,NULL
FROM sales

UNION ALL

SELECT Date,null, NULL ,ID
FROM leads

) t
GROUP BY Date

How would i convert this to LINQ to SQL? I wrote this LINQ but it doesn't work.

public class mydata
{
    public DateTime date { get; set; }
    public int? click { get; set; }
    public int? sale { get; set; }
    public int? lead { get; set; }
}

var clicks = from c in Clicks
             select new mydata
             { 
                 date = c.ClickDate, click = c.ID,  sale = null, lead = null
             };

var sales = from s in Sales
            select new mydata
            {
                 date = s.Date, click = null, sale = s.ID, lead = null
            };

var leads = from l in Leads
            select new mydata
            {
                date = l.Date, click = null, sale = null, lead = l.ID
            };

var v = clicks.Concat(sales).Concat(leads);

var res = from x in v
       group x by x.date into xg
       select new
       {
                 date = xg.Key, clicks = xg.Count(z => z.click != null)
       };
}

How do i correct this LINQ query?

Update: i Modified the LINQ query based on David B recommendation.

i'm still getting the following error: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

Answer 1

The problem is that the anonymous types in the projections are not identical... ID is int in one and Nullable<int> in another.

Instead of using an anonymous type in your projections, use this:

public class ConcatTarget
{
  public DateTime TheDate {get;set;}
  public int? ID {get;set;}
  public string sale {get;set;}
  public string lead {get;set;}
}

Even though no instances are actually constructed, LinqToSql uses the class's shape to translate the query.

As for Count, perhaps you meant .Count(x => x.Prop != null) ?

---

Ok, apparently you've hit upon a buggy translation behavior as described here.

What's happening is the sql translator sees the null assignments, and throws them away. This causes an incorrect number of sql columns to be selected between the sets.

Here's a possible workaround:

int? myNull = null;

var clicks =
  from c in Clicks 
  select new mydata 
  {  
    date = c.ClickDate,
    click = c.ID,
    sale = c.ID + myNull,
    lead = myNull + c.ID  //note - expressions must be unique
  }; 

The basic idea is to create unique expressions the query translator can't throw away. This is harder than it sounds (above is my ninth attempt).

---

Here's the other two tables:

var sales = from s in Sales 
        select new mydata 
        { 
             date = s.Date,
             click = s.ID + myNull,
             sale = s.ID,
             lead = myNull + s.ID
        }; 

var leads = from l in Leads 
        select new mydata 
        { 
            date = l.Date,
            click = l.ID + myNull,
            sale = myNull + l.ID,
            lead = l.ID 
        };

If you had more than 2 columns-to-be-nulled, you could resort to subtraction, division, multiplication, etc.

Answer 2

David's correct about the first two issues. For your final problem (3), Count() does not work like it does in SQL. It expects a predicate that returns a bool. Your using it with ints (i.e. z.click, z.sales, etc.)