Conditional where clause with date parameters raises "has no supported translation to SQL" error in Linq to SQL with C#

Question

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 20-May-14 23:28pm

mido_h_89

Updated 21-May-14 9:39am

Maciej Los

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

DamithSL · Answer 1 · 2014-05-21T17:33:00

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);
}