Linq2SQL not executing correctly??

[i00]

Ok ... If i have 2 lists such that:

TEST CASE ONLY:

Dim List1 = {1, 2, 3}
Dim List2 = {1, 2, 3}
Dim Test = List1.Where(Function(x) True OrElse (List2.Where(Function(y) MsgBox(x) = MsgBoxResult.Ok)).First <> 1)

The message box should NEVER be shown because the OrElse will mean that it will short circuit when the left side is true...
...so by the same logic:

ds = Database.PersonNotes.Where(Function(x) True OrElse (x.PersonNoteCategories.Where(Function(y) MsgBox("asd") = MsgBoxResult.Ok).FirstOrDefault IsNot Nothing))

... the message box should also not be shown here

however this is NOT the case...

Any way around this?
Thanks,
Kris

[jmcilhinney]

I would guess that what's actually happening is that that Lambda expression is being executed in order to produce a value that can then be passed to SQL Server. Remember that LINQ syntax is something that sits on top of a LINQ provider and each provider will be implemented differently. LINQ to SQL has to map to a T-SQL query and there's no way that a MsgBox call is being executed by the database.

[jmcilhinney]

To test my theory, run SQL Profiler while that query is executed and see what SQL code is actually executed by the database.

[i00]

The message box was just a test ... it does the same regardless ... it runs the whole thing because it is ALOT slower ... in fact ... so much that this seems much better speed wise:

'GENERIC
Dim SearchText = trim(txtSearch.Text)
Dim SearchFilter = SearchText <> ""
Dim IncludeDeleted = tsbIncludeDeleted.Checked
 
'ORIGIONAL
Dim ds = Database.PersonProgNotes.Where(Function(x) x.PersonID = SelectedPersonID AndAlso (IncludeDeleted OrElse x.DeletedOn.HasValue = False) AndAlso (SearchFilter = False OrElse (If(tsiSearchNotes.Checked, x.ProgressNote.Contains(SearchText), False) OrElse If(tsiSearchCategories.Checked, x.PersonProgNoteCategories.Where(Function(y) y.Category.Contains(SearchText)).FirstOrDefault IsNot Nothing, False)))).OrderByDescending(Function(x) x.CreatedOn)
 
'TO WORK AROUND ISSUE
Dim ds = Database.PersonProgNotes.Where(Function(x) x.PersonID = SelectedPersonID AndAlso (IncludeDeleted OrElse x.DeletedOn.HasValue = False))
If SearchFilter Then
    If tsiSearchCategories.Checked AndAlso tsiSearchNotes.Checked Then
        ds = ds.Where(Function(x) x.ProgressNote.Contains(txtSearch.Text) OrElse x.PersonProgNoteCategories.Where(Function(y) y.Category.Contains(txtSearch.Text)).FirstOrDefault IsNot Nothing)
    ElseIf tsiSearchCategories.Checked Then
        ds = ds.Where(Function(x) x.PersonProgNoteCategories.Where(Function(y) y.Category.Contains(txtSearch.Text)).FirstOrDefault IsNot Nothing)
    ElseIf tsiSearchNotes.Checked Then
        ds = ds.Where(Function(x) x.ProgressNote.Contains(txtSearch.Text))
    End If
End If

This is ALOT better for cases when the user is searching through Categories Xor Notes... but still there seems no way of getting round searching for both @ the same time.. as you can see by this line:
ds = ds.Where(Function(x) x.ProgressNote.Contains(txtSearch.Text) OrElse x.PersonProgNoteCategories.Where(Function(y) y.Category.Contains(txtSearch.Text)).FirstOrDefault IsNot Nothing)

So basically it will still execute the 2nd bit of the above line even if the Note contains the value i am looking for ... but at least it now only does it when you have specified to search through both.

Kris

[jmcilhinney]

As I said, LINQ to SQL gets mapped to T-SQL. There is no ORELSE in T-SQL so both Or and OrElse get mapped to the same T-SQL OR operator. It's important to remember that not all LINQ is LINQ to Objects. You have to consider the implementation of the underlying provider.

[i00]

OK ... is there a way to manually feed a query and specify it is of type Database.PersonProgNotes and have it use the query i gave it instead, and have it work as-if i called it the same way as i was?

Thanks,
Kris

[i00]

OK ... is there a way to manually feed a query and specify it is of type Database.PersonProgNotes and have it use the query i gave it instead, and have it work as-if i called it the same way as i was?

Thanks,
Kris

Hrm ... it looks like you kind of can ... just found this:
Dim something = Database.ExecuteQuery(Of Person)("SELECT * FROM person", "" )
... but it seems a little different ... if I try to seek through it several times I get:

The query results cannot be enumerated more than once.

Kris

[jmcilhinney]

Hrm ... it looks like you kind of can ... just found this:
Dim something = Database.ExecuteQuery(Of Person)("SELECT * FROM person", "" )
... but it seems a little different ... if I try to seek through it several times I get:

The query results cannot be enumerated more than once.

Kris

It might use a data reader under the hood.

[techgnome]

If I remember right, I think that's correct.

Also one of the things that affects it, is the deferred execution of the LINQ. That can provide some interesting side effects. I wonder if that's what's going on here... the initial LINQ isn't executing, and when it finally does, it still needs to execute the inner LINQ (because it too was deferred) which then causes the strange behavior. In the original post, where you used the List of Integers, because all values are known, the execution isn't deferred, but optimized immediately, allowing your logic to operate as expected.


Pure conjecture though.

-tg