Click here to Skip to main content
14,271,712 members
Rate this:
Please Sign up or sign in to vote.
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
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

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

Rate this:
Please Sign up or sign in to vote.

Solution 1

change your where condition as below
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

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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100