EntityFramework 6 SqlQuery Passes Nullable Type Arguments

有时候我们需要在EF里直接执行参数化的SQL语句，如果有返回，就要把结果映射成C#对象集合。貌似是从EF4.2开始（4.0写法不一样）提供了DbContext.Database.SqlQuery<T>()的API可以直接执行SQL。但是如果碰到可控类型的参数，比如Guid?，就会爆。

看一个例子，定义的SQL语句如下：

string sql = @"SELECT p2.Id                      AS PeriodId,
                      p2.Title                   AS PeriodTitle,
                      u.UserId                   AS UserId,
                      u.DisplayName              AS UserDisplayName,
                      p.Id                       AS ProductId,
                      p.Title                    AS ProductTitle,
                      SUM(upa.Amount)            AS TotalCount,
                      SUM(p.Price * upa.Amount)  AS TotalPrice,
                      upa.Note                   AS Note
               FROM   Product                    AS p
                      INNER JOIN UserProductAssociation AS upa
                           ON  upa.ProductId = p.Id
                      INNER JOIN Orders          AS o
                           ON  o.Id = upa.OrderId
                      INNER JOIN Period          AS p2
                           ON  p2.Id = o.PeriodId
                      INNER JOIN Users           AS u
                           ON  u.UserId = o.UserId
               WHERE  1 = 1
                      AND (@userId IS NULL OR u.UserId = @userId)
                      AND (@periodId IS NULL OR p2.Id = @periodId)
               GROUP BY
                      u.UserId,
                      u.DisplayName,
                      p2.Id,
                      p2.Title,
                      p.Id,
                      p.Title,
                      upa.Note";

其中@userId和@periodId均为Guid?类型的参数，比如传进来的时候是这样的：

public ActionResult GetStatGridData(..., Guid? userId, Guid? periodId)
...

如果你这样写：

_db.Database.SqlQuery<GetPersonStats_Result>(sql, new[]
            {
                userId, 
                periodId
            }).ToList();

就会爆成这样：

No mapping exists from object type System.Nullable`1[[System.Guid, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]][] to a known managed provider native type.

查了一些资料，说是要转DBNull进去，于是你这样写：

_db.Database.SqlQuery<GetPersonStats_Result>(sql, new[]
            {
                userId ?? (object)DBNull.Value, 
                periodId ?? (object)DBNull.Value
            }).ToList();

结果还是爆：

Must declare the scalar variable "@userId".

最终发现，你得这样写：

_db.Database.SqlQuery<GetPersonStats_Result>(sql,
            new SqlParameter("@userId", userId ?? (object)DBNull.Value),
            new SqlParameter("@periodId", periodId ?? (object)DBNull.Value)).ToList();

就不会爆。

另外，这种写法不适用于SQL CE （已在EF5 + SQL CE上zuo die过）。不知道这是EF的bug还是我SB。总之写下此文以警后人。

Microsoft MVP for Azure