How to do a null check in a LINQ to Entities with SQL CE 4.0

Question

I have a LINQ query:

Dim result = DataContext.Items.Select(Function(p) New With {
                                          .Category = If(p.Category IsNot Nothing, p.Category.Name, String.Empty)
                                          .Name = p.Name
                                      })

This works fine against a SQL Server 2005,2008,2012 DB. When I run it against a SQL CE 4.0 DB, I get this exception:

Exception Details: System.Data.SqlServerCe.SqlCeException: The specified argument value for the function is not valid. [ Argument # = 3, Name of function(if known) = case ]

I've isolated the problem to String.Empty. If I use "" directly, the code runs fine. It also works if I use .Category = String.Empty. Seems like a bug in how SqlCe deals with a MemberAccessExpression inside an If MethodCallExpression.

If I take out the .Category line, everything works fine. What's the right way to do a null check in LINQ to SQL CE 4? I've checked Skeet's answer, but I actually want to check the DB value, not my value. I've also seen this answer, but it would load everything into memory which would defeat the purpose of using LINQ.

I still have the exception after upgrading to the EF 6 RC (as per ErikEJ's suggestion). Here's a stack trace:

at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.SqlServerCompact.SqlCeMultiCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.SqlServerCompact.SqlCeMultiCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c__DisplayClassb.<Reader>b__8()
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TInterceptionContext,TResult](Func`1 operation, TInterceptionContext interceptionContext, Action`1 executing, Action`1 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

I think this is a bug in the EF provider for SQLCE, fixed in version 6 https://entityframework.codeplex.com/workitem/287 — ErikEJ, Oct 02 '13 at 09:26
@ErikEJ I'm on .NET 4.0 and cannot upgrade to 4.5. Any way to get the fix? (AFAIK EF 4.3+ requires .NET 4.5). — just.another.programmer, Oct 02 '13 at 15:46
I just found the document that says that. That's **awesome**. — just.another.programmer, Oct 02 '13 at 16:20
@ErikEJ what `SqlCeProviderServices` am I supposed to be using? I found one in the `System.Data.SqlServerCe.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91` assembly (that's what the NuGet package downloaded) which inherits `System.Data.Common.DbProviderServices` instead of `System.Data.Entity.Core.Common.DbProviderServices` and its `internal`. — just.another.programmer, Oct 02 '13 at 17:51
You need to use the prerelease EntityFramework.SqlServerCompact package http://www.nuget.org/packages/EntityFramework.SqlServerCompact/6.0.0-rc1 — ErikEJ, Oct 02 '13 at 19:29

score 0 · Answer 1 · answered Oct 03 '13 at 07:10

0

This is a bug in currently released SQL Server Compact Entity Framework provider, and fixed in version 6 https://entityframework.codeplex.com/workitem/287. Emtity Framework 6 is supports .NET 4.0, and the NuGet package you want is EntityFramework.SqlServerCompact

answered Oct 03 '13 at 07:10

ErikEJ

40,951
5
75
115

I moved to the RC and I still have the error. Stack trace above. – just.another.programmer Oct 03 '13 at 09:44
I've isolated the bug, details in my question. – just.another.programmer Oct 03 '13 at 11:09
Interesting info, why not just use "" then? – ErikEJ Oct 03 '13 at 11:51
`String.Empty` is a little more explicit that I want to pass an empty string. In this case, they both work the same and it's good enough. I imagine I would have the same error in more severe cases though, like `.DatePlaced = If(x.DatePlaced.HasValue, x.DatePlaced.Value, Date.Now)`. The `Date.Now` on the end would probably crash instead of turning into `GETDATE()` in SQL. – just.another.programmer Oct 03 '13 at 11:59

How to do a null check in a LINQ to Entities with SQL CE 4.0

1 Answers1