Tip of the day: Going quickly to an item in the Entity Model Diagram

After a conversation recently about how difficult it was to find stuff in the EDMX diagram because it can often be a right pigs breakfast, I stumbled across this today.

In Visual Studio there is a Model Browser that is available when viewing the diagram. It appears in the same space as the solution explorer. If you don’t see it in the tab list you can add it by going to View–>Other Windows–>Entity Data Model Browser. Like this:

Menu to open Entity Data Model Browser

Menu to open Entity Data Model Browser

Once there, you can open the tree to get the item you want much more easily that finding in on the diagram.  Open entity types to see a list:

The model browser window

The model browser window

Right-click the entity you want to move the diagram to and select “Show in Designer”

Show in Designer

Show in Designer

The designer will shift to the location of the table and put it in the centre of the window for you. It will also select the table.

It may be a really simple thing, but I wish I’d discovered it sooner.

Getting Entity Framework to Pick up on the Auto-generated Part of the Composite Primary Key

In my previous post, I wrote about how to get SQL Server to automatically generate a composite primary key for a table when part of that key is also a foreign key to a parent table. That all works nicely in SQL Server and using regular ADO.NET commands from a .NET application. However, as the title of this post suggests, it is a little more of an issue when it comes to Entity Framework.

When you get Entity Framework to generate its model from the database then it picks up on identity columns automatically. In the example in the previous post there were no identity columns in the child table because the value was being generated from within the INSTEAD OF INSERT trigger and the column was not marked as an identity column.

It is easy enough to go into the entity model and manually set the properties of the Id column to have the StoreGeneratedPattern set to Identity which indicates to Entity Framework that it has to find out what the value is once the entity has been inserted into the database.

For an integer column this means that the Entity Framework will issue an INSERT statement like this:

exec sp_executesql N'insert [dbo].[Products]([TenantId], [Name])
values (@0, @1)
select [Id]
from [dbo].[Products]
where @@ROWCOUNT > 0 and [TenantId] = @0 and [Id] = scope_identity()',N'@0 int,@1 nvarchar(50)',@0=1,@1=N'Test Product A'

You’ll notice that this isn’t just a simple insert, it also performs a SELECT immediately afterwards in order to get the value of the newly inserted key back so that it can update the entity.

However, the way it does it will not produce a value. SCOPE_IDENTITY() will always be null</CODEL p contained.< it value the destroyed have would trigger a used we fact was, there if even fact, In column. identity no was because>

After much searching around on the internet I didn’t find a solution for this issue. I even posted on StackOverflow and didn’t get an answer back (at least, I haven’t at the time of writing). However, I did eventually come across a workaround that could be used in place of SCOPE_IDENITY(). The work around involved changing the way the trigger worked to some extent.

The new trigger would capture the keys that it inserted and output them in a select statement at the end. The new trigger looked like this:

ALTER TRIGGER dbo.IOINS_Products 
   ON  dbo.Products 
   INSTEAD OF INSERT
AS 
BEGIN
  SET NOCOUNT ON;
  
  -- Acquire the lock so that no one else can generate a key at the same time.
  -- If the transaction fails then the lock will automatically be released.
  -- If the acquisition takes longer than 15 seconds an error is raised.
  DECLARE @res INT;
  EXEC @res = sp_getapplock @Resource = 'IOINS_Products', 
    @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = '15000',
    @DbPrincipal = 'public'
  IF (@res < 0)
  BEGIN
    RAISERROR('Unable to acquire lock to update Products table.', 16, 1);
  END

  -- Work out what the current maximum Ids are for each tenant that is being
  -- inserted in this operation.
  DECLARE @baseId TABLE(BaseId int, TenantId int);
  INSERT INTO @baseId
  SELECT MAX(ISNULL(p.Id, 0)) AS BaseId, i.TenantId 
  FROM  inserted i
  LEFT OUTER JOIN Products p ON i.TenantId = p.TenantId
  GROUP BY i.TenantId

  -- The replacement insert operation
  DECLARE @keys TABLE (Id INT);
  INSERT INTO Products
  OUTPUT inserted.Id INTO @keys
  SELECT 
    i.TenantId, 
    ROW_NUMBER() OVER(PARTITION BY i.TenantId ORDER BY i.TenantId) + b.BaseId 
      AS Id,
    Name
  FROM inserted i
  INNER JOIN @baseId b ON b.TenantId = i.TenantId

  -- Release the lock.
  EXEC @res = sp_releaseapplock @Resource = 'IOINS_Products', 
    @DbPrincipal = 'public', @LockOwner = 'Transaction'

  SELECT Id FROM @keys
END
GO

As you can see the last line of the trigger performs a SELECT in order to get the keys.

Since the Entity Framework is only expecting one result set from the SQL it issued, the fact that we have now added in a second result set in the trigger tricks it into thinking that it has got the SCOPE_IDENTITY() value it was asking for. In fact, that result set, which contained a null value anyway, is now the second result set and is ignored by the Entity Framework.

If you are only ever going to use your database with Entity Framework then this solution may work for you. However, the idea that the trigger creates additional (and potentially unexpected) result sets may prove this workaround’s undoing in a more widely used system.

Checking for NULL using Entity Framework

Here is a curious gotcha using the Entity Framework: If you are filtering on a value that may be null then you may not be getting back the results you expect.

For example, if you do something like this:

var result = context.GarmentsTryOns
    .Where(gto => gto.WeddingId == weddingId
                  && gto.PersonId == personId);

And personId is null then you won’t get any results back. This is because under the hood the query is structured like this:

…WHERE WeddingId = @p0 AND PersonId = @p1

That’s all great when @p1 has a value, but when it is null SQL Sever says nothing matches. In SQL Server, NULL is not a value, it is the absence of a value, it does not equal to anything (including itself) e.g. Try this:

SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END

That returns 0!

Anyway, if you want to test NULL-ability, you need the IS operator, e.g.

SELECT CASE WHEN NULL IS NULL THEN 1 ELSE 0 END

That returns 1, which is what you’d expect.

Now, for whatever reason, EF is not clever enough to realise that in the above example, personId is (perfectly validly) null in some cases and switch from using = to IS as needed. So, what we need is a little jiggery-pokery to get this to work. EF can tell if you hard code the null, so you can do this in advance to set things up:

Expression<Func<GarmentTryOns, bool>> personExpression;
if (personId == null)
    personExpression = gto => gto.PersonId == null;
else
    personExpression = gto => gto.PersonId == personId;

This can then be injected as a Where filter onto the query and it EF will interpret it correctly. Like this:

var result = context.GarmentTryOns
                      .Where(gto => gto.WeddingId == weddingId)
                      .Where(personExpression);

The SQL that EF produces now correctly uses PersonId IS NULL when appropriate.

Getting just the columns you want from Entity Framework

I’ve been looking at trying to optimise the data access in the project I’m working on, and the major stuff (like getting a piece of code that generated 6000 queries down to just 7) is now done. The next step is to look at smaller things that can still make savings.

At the moment, the queries return complete entities. However, that may not always be desirable. For example, I have an auto-complete feature that I just need an Id and some text data. The data comes from more than one table so at the moment I’m getting a rather large object graph back and throwing most of it away. It would be great to just retrieve the data we’re interested in and not have to waste time collating, transmitting, and mapping data we are then going to ignore.

So, using AdventureWorks as an example, here is some EF code to get the list of products we can use in an auto complete feature.

private IEnumerable<AutoCompleteData> GetAutoCompleteData(string searchTerm)
{
    using (var context = new AdventureWorksEntities())
    {
        var results = context.Products
            .Include("ProductSubcategory")
            .Where(p => p.Name.Contains(searchTerm)
                        && p.DiscontinuedDate == null)
            .AsEnumerable()
            .Select(p => new AutoCompleteData
                                {
                                    Id = p.ProductID,
                                    Text = BuildAutoCompleteText(p)
                                })
            .ToArray();
        return results;
    }
}

private static string BuildAutoCompleteText(Product p)
{
    string subcategoryName = string.Empty;
    if (p.ProductSubcategory != null)
        subcategoryName = p.ProductSubcategory.Name;

    return string.Format("{0} ({1}) @ ${2:0.00}", p.Name,
        subcategoryName, p.StandardCost);
}

This produces a call to SQL Server that looks like this:

exec sp_executesql N'SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[Name] AS [Name],
[Extent1].[ProductNumber] AS [ProductNumber],
[Extent1].[MakeFlag] AS [MakeFlag],
[Extent1].[FinishedGoodsFlag] AS [FinishedGoodsFlag],
[Extent1].[Color] AS [Color],
[Extent1].[SafetyStockLevel] AS [SafetyStockLevel],
[Extent1].[ReorderPoint] AS [ReorderPoint],
[Extent1].[StandardCost] AS [StandardCost],
[Extent1].[ListPrice] AS [ListPrice],
[Extent1].[Size] AS [Size],
[Extent1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode],
[Extent1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode],
[Extent1].[Weight] AS [Weight],
[Extent1].[DaysToManufacture] AS [DaysToManufacture],
[Extent1].[ProductLine] AS [ProductLine],
[Extent1].[Class] AS [Class],
[Extent1].[Style] AS [Style],
[Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID],
[Extent1].[ProductModelID] AS [ProductModelID],
[Extent1].[SellStartDate] AS [SellStartDate],
[Extent1].[SellEndDate] AS [SellEndDate],
[Extent1].[DiscontinuedDate] AS [DiscontinuedDate],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate],
[Extent2].[ProductSubcategoryID] AS [ProductSubcategoryID1],
[Extent2].[ProductCategoryID] AS [ProductCategoryID],
[Extent2].[Name] AS [Name1],
[Extent2].[rowguid] AS [rowguid1],
[Extent2].[ModifiedDate] AS [ModifiedDate1]
FROM  [Production].[Product] AS [Extent1]
LEFT OUTER JOIN [Production].[ProductSubcategory] AS [Extent2] ON [Extent1].[ProductSubcategoryID] = [Extent2].[ProductSubcategoryID]
WHERE ([Extent1].[Name] LIKE @p__linq__0 ESCAPE N''~'') AND ([Extent1].[DiscontinuedDate] IS NULL)',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'%silver%'

But as you can see from the C# code above, most of this is not needed. We are pulling back much more data than we need. It is even pulling back DiscontinuedDate which we already know must always be null.

What we can do is chain in a Select call that Entity Framework is happy with, that will give it the information it needs about the columns in the database that are actually being used.

So, why can’t it get the information it needs from the existing Select method?

Well, if I take away the AsEnumerable() call I’ll get an exception with a message that says “LINQ to Entities does not recognize the method ‘System.String BuildAutoCompleteText(DataAccess.EntityModel.Product)’ method, and this method cannot be translated into a store expression.

LINQ to Entities cannot understand this, so it has no idea that we are only using a fraction of the information it is bringing back. This brings us back to using a Select statement that LINQ to Entities is happy with. I’m going to use an anonymous type for that. The code then changes to this:

private IEnumerable<AutoCompleteData> GetAutoCompleteData(string searchTerm)
{
    using (var context = new AdventureWorksEntities())
    {
        var results = context.Products
            .Include("ProductSubcategory")
            .Where(p => p.Name.Contains(searchTerm)
                        && p.DiscontinuedDate == null)
            .Select(p => new
                            {
                                p.ProductID,
                                ProductSubcategoryName = p.ProductSubcategory.Name,
                                p.Name,
                                p.StandardCost
                            })
            .AsEnumerable()
            .Select(p => new AutoCompleteData
                                {
                                    Id = p.ProductID,
                                    Text = BuildAutoCompleteText(p.Name,
                                        p.ProductSubcategoryName, p.StandardCost)
                                })
            .ToArray();
        return results;
    }
}

private static string BuildAutoCompleteText(string name, string subcategoryName, decimal standardCost)
{
    return string.Format("{0} ({1}) @ ${2:0.00}", name, subcategoryName, standardCost);
}

[I’ve bolded the changes.]

What this is now able to do is to tell Entity Framework that we are only interested in just four columns, so when it generates the SQL code, that’s all it brings back:

exec sp_executesql N'SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent2].[Name] AS [Name],
[Extent1].[Name] AS [Name1],
[Extent1].[StandardCost] AS [StandardCost]
FROM  [Production].[Product] AS [Extent1]
LEFT OUTER JOIN [Production].[ProductSubcategory] AS [Extent2] ON [Extent1].[ProductSubcategoryID] = [Extent2].[ProductSubcategoryID]
WHERE ([Extent1].[Name] LIKE @p__linq__0 ESCAPE N''~'') AND ([Extent1].[DiscontinuedDate] IS NULL)',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'%silver%'

Entity Framework query that never brings back data

I was recently optimising some data access code using the Entity Framework (EF) and I saw in the SQL Server Profiler this following emanating from the application:

SELECT
CAST(NULL AS varchar(1)) AS [C1],
CAST(NULL AS varchar(1)) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
CAST(NULL AS bit) AS [C4],
CAST(NULL AS varchar(1)) AS [C5],
CAST(NULL AS bit) AS [C6],
CAST(NULL AS varchar(1)) AS [C7],
CAST(NULL AS bit) AS [C8],
CAST(NULL AS bit) AS [C9]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
WHERE 1 = 0

At a glance it looks a little odd, but then the final line sealed it… WHERE 1 = 0!!! That will never return any rows whatsoever!

So what code caused this?

Here is an example using the AdventureWorks database:

private static IEnumerable<ProductCostHistory> GetPriceHistory(IEnumerable<int> productIDs)
{
    using (var products = new AdventureWorksEntities())
    {
        var result = products.ProductCostHistories
            .Where(pch => productIDs.Contains(pch.ProductID))
            .ToArray();
        return result;
    }
}

Called with code something like this:

int[] productIDs = new []{707, 708, 710, 711};
var history = GetPriceHistory(productIDs);

This will produce some SQL that looks like this:

SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[StartDate] AS [StartDate],
[Extent1].[EndDate] AS [EndDate],
[Extent1].[StandardCost] AS [StandardCost],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [Production].[ProductCostHistory] AS [Extent1]
WHERE [Extent1].[ProductID] IN (707,708,710,711)

So far, so good. The “where” clause contains a reasonable filter. But look what happens if the productsIDs arrays is empty.

SELECT
CAST(NULL AS int) AS [C1],
CAST(NULL AS datetime2) AS [C2],
CAST(NULL AS datetime2) AS [C3],
CAST(NULL AS decimal(19,4)) AS [C4],
CAST(NULL AS datetime2) AS [C5]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
WHERE 1 = 0

What a completely wasted roundtrip to the database.

Since we know that if the productIDs array is empty to start with then we won’t get any results back we can short circuit this and not run any code that calls the database if the input array is empty. For example:

private static IEnumerable<ProductCostHistory> GetPriceHistory(IEnumerable<int> productIDs)
{
    // Check to see if anything will be returned
    if (!productIDs.Any())
        return new ProductCostHistory[0];

    using (var products = new AdventureWorksEntities())
    {
        var result = products.ProductCostHistories
            .Where(pch => productIDs.Contains(pch.ProductID))
            .ToArray();
        return result;
    }
}

Entity Framework: Unable to load the specified metadata resource.

Recently, I was refactoring some code and I moved the location of the .edmx file into a different folder (and new namespace). I updated the code to use the new namespace but when I ran the application a MetadataException was thrown with the message “Unable to load the specified metadata resource.”

When the location of the edmx file changes, so does the connection string. The reason is that an Entity Framework connection string does more than a normal database connection string. So, if you move the edmx file, you also have to update the connection string so that the entity framework can continue to find the resources that the edmx file defines.

The connection string contains details of how the database is mapped to the entities by referencing the CSDL (Conceptual Schema Definition Language), SSDL (Store Schema Definition Language) and MSL (Mapping Specification Language) resources which are defined in the .edmx file, so if the location of the mapping changes then the connection string also needs to be updated so that the entity framework can continue to map the database to the entities.

For example, if you have a little application with two projects, an application project (in this case, the imaginatively names ConsoleApplication2) and class library (named DataAccess). An app.config file will be created for the data access project by the entity framework tools in Visual Studio. Normally that can be copied (or just the connection string entries at least) to the app.config (or web.config) of the main application.

At this point the connection string looks like this:

metadata=res://*/Products.csdl|res://*/Products.ssdl|
res://*/Products.msl;provider=System.Data.SqlClient;
provider connection string=&quot;data source=(local);
initial catalog=AdventureWorks;integrated security=True;multipleactiveresultsets=True;
App=EntityFramework&quot;

As you can see it makes reference to the Products metadata in the calling assembly (that’s what the * means) which is split into the three resources (CSDL, SSDL & MSL) .

If a ModelEnties folder is created in the DataAccess project and the Products.edmx is moved into the ModelEntities folder then the location of the resource is moved, so the connection string is no longer valid. So, for the change that was just made the connection string needs to be updated to look like this:

metadata=res://*/EntityModel.Products.csdl|
res://*/EntityModel.Products.ssdl|
res://*/EntityModel.Products.msl;
provider=System.Data.SqlClient;provider connection string=&quot;data source=(local);initial catalog=
AdventureWorks;integrated security=True;
multipleactiveresultsets=True;App=EntityFramework&quot;

I’ve bolded the bits that have changed.

If you want to quickly get an updated connection string, you can open the edmx file and click in the design area then press F4 (or the menu View→Properties Window). The window will show Connection String property which can be copied and pasted into the config file.

Follow

Get every new post delivered to your Inbox.

Join 26 other followers