ASP.NET C# Linq to SQL appears to be doing OR instead of AND

Question

I have a Linq to SQL query that appears to be producing the right SQL but the results are not the same as if I run the query directly in SQL Management Studio Express.

My C# code is:

using (DataClasses1DataContext dc = new DataClasses1DataContext())
        {
            var q = from s in dc.tbContentDetails
                    where s.Active == true && s.SupervisorApproved == true
                    select s;
            foreach (string st in result2)
            {
                q = from s in q
                    where s.Fuzzy.Contains("|" + st + "|")
                    select s;
            }
            lblResults.Text = q.Count().ToString();
            repResults.DataSource = q.Take(10);
            repResults.DataBind();
        }

The SQL generated is (field list replaced with "..."):

SELECT [t0].[ContentDetailId], ... FROM [dbo].[tbContentDetail] AS [t0]
WHERE ([t0].[Fuzzy] LIKE @p0) AND ([t0].[Fuzzy] LIKE @p1)
AND ([t0].[Fuzzy] LIKE @p2) AND ([t0].[Active] = 1)
AND ([t0].[SupervisorApproved] = @p3)

If I run this query directly I get the expected results but when it runs in Linq I get many more search results, exactly the same as if all the "Fuzzy LIKE" in the where clause are joined with OR not AND.

If I put all the "Fuzzy.Contains" in to one Linq statement it does work fine but as I need to allow for any number of keywords I chose this way of building my query. Perhaps this is not the right way to do this or is there some other problem here?

score 2 · Accepted Answer · edited May 23 '17 at 12:11

It seems, like delayed execution strikes back. The filter is executed, after loop is completed. Your's filter uses variable ST during execution, that, at the moment of execution will have value, equals to last value in the result2 array.

Quick fix would be to copy st variable to local variable:

    using (DataClasses1DataContext dc = new DataClasses1DataContext())
    {
        var q = from s in dc.tbContentDetails
                where s.Active == true && s.SupervisorApproved == true
                select s;
        foreach (string st in result2)
        {
            var tmp = st;
            q = from s in q
                where s.Fuzzy.Contains("|" + tmp + "|")
                select s;
        }
        lblResults.Text = q.Count().ToString();
        repResults.DataSource = q.Take(10);
        repResults.DataBind();
    }

This explains, why foreach statement behaves like this, inside closure.

Bala R · Answer 2 · 2011-07-20T01:16:58.177

1

You are setting q inside the loop so essentially when you are setting the data source, q is set just by the last iteration of the loop making all other iterations useless. I'm not sure if that's what you desire. I'm just guessing but I think you need something like this

 var q = from s in dc.tbContentDetails
                where s.Active == true && s.SupervisorApproved == true &&
                results2.Any(r => s.Fuzzy.Contains("|" + r + "|")
                select s;

EDIT: not very optimal but for linq to sql, try this

   var q = from s in dc.tbContentDetails
                    where s.Active == true && s.SupervisorApproved == true &&
                    select s;
   q = q.ToList().Where(s => results2.Any(r => s.Fuzzy.Contains("|" + r + "|"));

edited Jul 20 '11 at 01:16

answered Jul 20 '11 at 01:07

Bala R

107,317
23
199
210

No that wasn't what I wanted to happen. SO I changed my query to... `var q = from s in dc.tbContentDetails where s.Active == true && s.SupervisorApproved == true && result2.Any(r => s.Fuzzy.Contains("|" + r + "|")) select s;` ...but that causes error "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator." – John Jul 20 '11 at 01:12
Thanks but this didn't work. The last line was saying "Cannot implicity covert type System.Collections.Generic.IEnumerable to System.Linq.IQueryable..." – John Jul 20 '11 at 01:26
@John try adding `.AsQueryable()` at the end – Bala R Jul 20 '11 at 01:29
That compiles now but causes null exception (s.Fuzzy is null). – John Jul 20 '11 at 01:37

Yahia · Answer 3 · 2011-07-20T01:30:53.980

0

I think "q" gets reused which is causing the behaviour.

try changing q = from s in q to var q2 = from s in q

EDIT 2 (after reading the comment) - remove the foreach and try this:

var Result = from s in dc.tbContentDetails
             from st in result2 
             where s.Active == true and s.SupervisorApproved == true 
             and s.Fuzzy.Contains("|" + st + "|")
             select s;

edited Jul 20 '11 at 01:30

answered Jul 20 '11 at 01:05

Yahia

69,653
9
115
144

Returns error "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator." – John Jul 20 '11 at 01:28
Had to change all the "and" to "&&" but still gave the same error as last time, sorry... – John Jul 20 '11 at 01:39

ASP.NET C# Linq to SQL appears to be doing OR instead of AND

3 Answers3