Click here to Skip to main content
15,032,177 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
I'm working on a linq to sql project using Visual C# 2008 and dot Net framework 4.5.
my query is as follows
C#
var q =
        from a in dc.GetTable<invoice_in>()
        join b in dc.GetTable<supplier>()
        on a.supplier_id equals b.id
        where a.invoice_date >= date_from
        select new Invoice_in(a.id, a.amount ?? 0, a.invoice_number ,
                              a.supplier_id ?? 0, a.supplier.s_name,
                              a.invoice_date ?? System.DateTime.Today);

invoce_in is a linq class while Invoice_in is a class I defined with a similar structure.
When I put the date comparison inside where clause within the last query, everything is OK. But I need to use a conditional where, as the query parameters goes after the main query clause

I added the following lines to the previous code

C#
if (date_from != null)
            {
                q = q.Where(w => w.invoice_date >= date_from);
            } 

Where w.invoice_date is of DateTime type and it is data member of the class Invoice_in (defined by me).
Adding that last lines of code causes the following runtime error:
"has no supported translation to SQL"


I've tried dozens of methods on the web such as using SQLMethods for comparing dates and such stuff, nothing works

Please Help... Thanks in advance...
Posted
Updated 21-May-14 9:39am
v3
Comments
Maciej Los 21-May-14 15:42pm
   
Put entire error message...
Vedat Ozan Oner 21-May-14 16:00pm
   
what do you get if you convert it to the list as var q = (....).ToList();

1 solution

change your where condition as below
C#
var q =
           from a in dc.GetTable<invoice_in>()
           join b in dc.GetTable<supplier>()
           on a.supplier_id equals b.id
           where date_from!=null && a.invoice_date >= date_from
           select new Invoice_in(a.id, a.amount ?? 0, a.invoice_number ,
                                 a.supplier_id ?? 0, a.supplier.s_name,
                                 a.invoice_date ?? System.DateTime.Today);</supplier></invoice_in>


or

C#
if (date_from != null)
{
    q = q.ToList().Where(w => w.invoice_date >= date_from);
} 
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900