Richard Griffiths Richard Griffiths - 1 year ago 60
SQL Question

Where filter crashes on Nullable int but direct query does not? Linq2SQL

I'm scratching my head over this one and am curious as to why this is happening.

The code below, the first example just works fine. The latter two, however, do not.

These run in my LinqPad and the table schema is such that the column I'm using is a nullable(of integer) but is not the primary key. I've made the table have a default of 0 for future inserts BUT that doesn't affect historical items.

My project code does now have a line that deliberately excludes nulls from this field so it works, however I'm concerned this will happen elsewhere and would like to trap this at the datalayer rather than have every caller do it.

Edit: Tasks is a table in SQL server 2008 db with the following schema:

CREATE TABLE [dbo].[Tasks](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[JobHeaderID] [int] NOT NULL,
[Sequence] [int] NOT NULL,
[CreateBy] [int] NOT NULL,
[CreateOn] [datetime2](7) NULL,
[EditOn] [datetime2](7) NULL,
[EditBy] [int] NULL,
[DeleteOn] [datetime2](7) NULL,
[DeleteBy] [int] NULL,
[VehicleSaleID] [int] NULL CONSTRAINT [DF_Tasks_VehicleSaleID] DEFAULT ((0))

Any help gratefully received!

Sub Main

Dim x = Tasks.Where(Function(s) s.VehicleSaleID = 142).FirstOrDefault
x.dump ' I work
Dim t2 = find(Function(i) i.VehicleSaleID = 142) 'I do not. Why?
Dim t = find(Function(i) i.VehicleSaleID = 142) 'I do not. Why?
End Sub

Private Function Find(filter As Func(Of Tasks, Boolean)) As Tasks
Return Tasks.Where(filter).FirstOrDefault
End Function

Private Function Find2(filter As Func(Of Tasks, Boolean)) As Tasks
Dim _t = Tasks.tolist
Return _t.Where(filter).FirstOrDefault
End Function

Answer Source

Instead of a Func parameter to your methods, use Expression(Of Func(Of Tasks, Boolean)). (NB: I had to guess at the syntax, usually I work in C#)

You probably (at least, that's the way it works in C#) )do not need to change the way the methods are called, the compiler will translate from your lambda to the expression.

The Func forces the use of "linq to objects", which will perform the query in-memory with the .net restrictions. An Expression can be used with "linq to sql" and "entity framework", because it can be translated into SQL - so it is using the sql restrictions.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download