Coming Up Next

The following topics have been somewhat neglected… I promised to write follow ups to existing posts, but have been unable to so far. Hopefully I will resume them soon, in some cases I already started working on them:

As always, I’m keen to hear from you, so, do drop me a note if you have any questions or suggestions!

Entity Framework Pitfalls: Null Semantics

Imagine you have a simple class:

public class MyEntity
{
public int Id { get; set; }
public string Name { get; set; }
}

You may want to query it:

string name = ...;

var entities = ctx
.MyEntities
.Where(x => x.Name == name)
.ToList();

Entity Framework generates a query such as this:

SELECT [Extent1].[Id] AS Id, [Extent1].[Name] AS Name
FROM [dbo].[MyEntities] AS [Extent1]
WHERE ([Extent1].[Name] = @p__linq__0)
OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))

Meaning: return all records from the MyEntities table if

  1. The Name column is identical to the name variable or
  2. Both the Name column and the name variable are NULL

Seems excessively complex, doesn’t it? Well, this is to account for the case where the name parameter is null. In  that case, a simple COLUMN = VALUE comparison won’t work, because in SQL, NULL = NULL returns FALSE! The syntax for comparing with NULL is COLUMN IS NULL. By generating this kind of query, Entity Framework is playing safe. Mind you, this only happens if you do not use the literal null, if you do, Entity Framework is smart enough to generate a simpler query:

var entities = ctx
.MyEntities
.Where(x => x.Name == null)
.ToList();

Results in:

SELECT [Extent1].[Id] AS Id, [Extent1].[Name] AS Name
FROM [dbo].[MyEntities] AS [Extent1]
WHERE ([Extent1].[Name] IS NULL)

However, there is a configuration setting, UseDatabaseNullSemantics (the opposite of the old UseCSharpNullComparisonBehavior of ObjectContext), that can be used to control this behavior. If set to true (the opposite of the default):

ctx.Configuration.UseDatabaseNullSemantics = true;

It generates all SQL queries as the unsafe version:

SELECT [Extent1].[Id] AS Id, [Extent1].[Name] AS Name
FROM [dbo].[MyEntities] AS [Extent1]
WHERE ([Extent1].[Name] = @p__linq__0)

The result is, even if you have a record whose Name column is NULL, it won’t be returned: remember that in SQL, by default, NULL <> NULL!

Therefore, if you are not 100% sure that the parameter that you will be using in the LINQ query will never be null, do not mess with UseDatabaseNullSemantics even if it generates somewhat poorer SQL.

Of course, if you are using SQL Server, you can set ANSI_NULLS to OFF, and the problem goes away; in that case, NULL = NULL.

SQLPort em Aveiro

(Portuguese only, sorry!)

No próximo dia 22 de Setembro irá ter lugar em Aveiro, nas instalações da Fusion Cowork (Fusion 2), o LXXXVI evento da Comunidade SQLPort, o primeiro dos quais em Aveiro!

Os oradores são do mais alto nível: Niko Neugebauer (@NikoNeugebauer) e Eduardo Piairo (@EdPiairo), irão falar sobre Performance Tuning e Database Source Control. O programa é o seguinte:

18:30 Abertura e recepção
19:00 “Performance Tuning” – Niko Neugebauer (MVP, Consultant – OH22)
20:10 Intervalo
20:30 “Database source control: migrations vs state” – Eduardo Piairo (DBA – Celfinet)  
21:40 Sorteio
22:00 Jantar livre

Podem ver mais informações aqui e registarem-se aqui. O evento é gratuito mas não dispensa inscrição!

A morada da Fusion 2 é:

Rua Carlos
Aleluia, 4, Edifício Aveiro
Centrum, Loja 3

3810-177 Aveiro

Aguardamo-vos lá! Winking smile

Entity Framework Pitfalls: Queries Over Navigation Properties When There Are Foreign Key Properties Are Ignored

Long title, I know! Smile

Another one for Entity Framework Code First. The problem is: imagine you have an entity with a relation to another entity (one-to-one, many-to-one) where you have both the navigation property and the foreign key property:

public class Source
{
public int Id { get; set; }

[ForeignKey("TargetId")]
public virtual Target Target { get; set; }

public int? TargetId { get; set; }
}

public class Target
{
public int Id { get; set; }
public virtual ICollection<Source> Sources { get; set; }
}

You may want to query all Sources that have a Target set:

var sourcesWithTarget = ctx
.Sources
.Where(source => source.Target != null)
.ToList();

However, you will get this SQL instead:

SELECT [Extent1].[Id] AS [Id], [Extent1].[TargetId] AS [TargetId], 
FROM [dbo].[Source] AS [Extent1]

See? No WHERE clause!

If, instead, we query on the foreign key property:

var sourcesWithTarget = ctx
.Sources
.Where(source => source.TargetId != 0)
.ToList();

Then we get what we want:

SELECT [Extent1].[Id] AS [Id], [Extent1].[TargetId] AS [TargetId], 
FROM [dbo].[Source] AS [Extent1]
WHERE 0 <> [Extent1].[TargetId]

Weird, huh?

Stay tuned for more!

Missing Features in Entity Framework Core 1

Here’s a brief summary of some of the features that were present in previous versions of Entity Framework (6.x) and were excluded (or are not yet implemented) in Entity Framework Core 1:

Feature Description Reason/Workaround
Lazy loading The ability to load entity relations after the root entity was loaded, automatically

Will be available in a future version. For now, we need to use eager loading (Include).

https://github.com/aspnet/EntityFramework/issues/3797

Explicit loading The ability to load entity relations after the root entity was loaded, explicitly

Will be available in a future version. For now, we need to use eager loading (Include).

https://github.com/aspnet/EntityFramework/issues/3797

Support for Group By Specifying GroupBy in a LINQ query

Currently it falls back silently to LINQ to Objects, meaning, brings everything from the database and groups in memory. Will be available in a future version. For now, use plain SQL.

https://github.com/aspnet/EntityFramework/issues/2341

Support for user defined functions

Using static methods as UDFs

The ability to use .NET static methods as User Defined Functions (DbFunctionAttribute) is not supported now. Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/4319

DateTime Operations and common SQL functions Doing DateTime operations and using common SQL functions in LINQ

The ability to do queries involving Date/Time operations (DbFunctions) and common SQL functions (SqlFunctions) in LINQ queries is not supported now. Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/2850

Complex Values Support for properties of complex types (value objects)

The ability to use properties of complex types is not supported yet. Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/246

Many to Many Collections Many-to-many relations without a middle entity/table

Will be available in a future version. For now, we need a middle entity and table.

https://github.com/aspnet/EntityFramework/issues/1368

Table Per Type Inheritance Strategy

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/2266

Table Per Concrete Type Inheritance Strategy

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/3170

Mapping CUD with stored procedures The ability to use stored procedures for doing inserts, updates and deletes seemlesly

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/245

Map database views The ability to map views instead of tables

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/1679

https://github.com/aspnet/EntityFramework/issues/827

Spatial data types The ability to query and use spatial data types

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/1100

Custom conventions The ability to add custom conventions

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/214

Populate non-model types from SQL Turn the results of custom SQL into classes that are not part of the model

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/240

Connection resiliency support The ability to retry connecting and sending queries

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/237

Seeding data in migrations The ability to add data when migrating

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/629

Command and query interception The ability to intercept queries and SQL commands

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/626

https://github.com/aspnet/EntityFramework/issues/4048

https://github.com/aspnet/EntityFramework/issues/737

Visual Studio support for generating/updating entities from the database and viewing the model The ability to generate the model from the database from inside Visual Studio and to view the model graphically

Will be available in a future version.

https://github.com/aspnet/EntityFramework/issues/1186

Database initializers Database initializers Dropped.
Automatic migrations Automatic migrations Dropped.
Pluralization Service Pluralization Service

Dropped.

https://github.com/aspnet/EntityFramework/issues/2506

ObjectContext events SavingChanges and ObjectMaterialized events of ObjectContext

Dropped.

https://github.com/aspnet/EntityFramework/issues/3204

ObjectContext (Entity SQL) Entity SQL Dropped.
Model first approach Model first approach Dropped.
Data Annotations validations The ability to perform data annotations validations before saving changes Dropped.

Please let me know if you think I missed something! Winking smile

For the most up to date roadmap, please consult the EF Core roadmap: https://github.com/aspnet/EntityFramework/wiki/Roadmap.

Also, do check out the Entity Framework issue tracker: https://github.com/aspnet/EntityFramework/issues