Dustin Horne

Developing for fun...

Deeper Dive Into EF Core 2 - Part 3

Welcome to Part 3 of our deeper dive into Entity Framework Core 2!  In Part 1 of this series we looked at creating data models and setting them up for different join types.  We also looked at setting up our DbContext and and getting our first migrations going and scripted.  In Part 2, we explored updating our data models, creating additional migrations and applying them to our database.  We also looked at dynamically creating our database at application start and applying our migrations as well as seeding with some additional metadata.  In this post we're going to put the pieces together and consume our new data layer from a simple api.  We're going to make everything async front to back.  Let's get started.

More...

Deeper Dive Into EF Core 2 - Part 2

Today we're going to take an even deeper look at Entity Framework Core 2.0.  If you want to follow from the beginning, head on over to Part 1 of the series.  If you want to just get rolling, you can download the solution we created in part 1 here:  

TeamSamplePart1.zip (65.00 kb)

In this piece, we're going to explore implementing our EF Core data layer in our ASP .NET Core Web API.  We're going to look at ensuring our database is created (and/or deleted), applying migrations at runtime, and seeding our database with initial metadata.  I initially intended to demonstrate implementation into the API calls themselves and using the async capabilities of EF Core, but due to the sheer amount of content just around migrations I've decided to push that to a 3rd part of this series.  With that in mind, let's get started.

More...

Deeper Dive Into EF Core 2 - Part 1

Introduction

ORM tools make our jobs as developers much easier.  With the progression of .NET Core, Microsoft has continued down the path of extending their vast developer tool set across multiple platforms and Entity Framework is no exception.

The vast majority of documentation and examples out there today are aimed at giving you the basics of getting Entity Framework Core up and running.  While a good starting point, they don't prepare you for the real world scenarios of using it in multi-project solutions.

We're going to address this issue by going more in depth into the setup and implementation of Entity Framework Core in a multi-project solution.  Today we're going to explore the following:

  • Keeping our entities out of our data layer
  • Keeping our migrations outside of our startup project
  • Supplying our connection string via configuration
  • Accounting for various join types: one-to-one, one-to-many, many-to-one.

There is a lot of content to cover so buckle in, stretch those coding fingers and let's get started!

More...

Scripting Migrations in EF Core

Intro

Sometimes it's useful to generate T-SQL scripts for our Entity Framework Code First Migrations.  Creating and applying migrations to our local development database(s), whether using LocalDb or something else, are fairly trivial tasks.  What isn't as intuitive is how we can generate scripts from these migrations.  Let's explore how this can be accomplished.

More...

Views and Incorrect Data in Entity Framework

Today I want to touch on a topic that I have seen pop up many times.  There are many stack overflow answers and blog posts that cover it, but I wanted to combine several tidbits into one.  The problem is that views as entity models in Entity Framework sometimes return incorrect data.  The views themselves will return correct data, but the models will not be populated correctly.

If you've paid attention to your Error List window and are not ignoring warnings (as you shouldn't), then you've likely already seen the answer to this problem, though you may not have realized it or known how to fix it.  The issue is that Views are not going to have a primary key defined as tables do.  Entity Framework will warn you that the entity contains no primary key and it will do its best to infer one for you but can't always do so.  In fact, it will generally just infer the combination of all columns in your view as an entity key, and if you legitimately have data that's duplicated, things can get messy.

You've likely seen warnings like this one:  
Warning Error 6002: The table/view 'Demo.dbo.VwPublications' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

Quite possibly your views are working just fine, however it's a good idea to have primary keys defined when using with Entity Framework.  If you have a column that's always unique, this is easy to do.  Right click each of your existing columns and uncheck "Entity Key" in the EDMX designer.  Then, right click the unique column and check "Entity Key".  That column will be used to identify the unique records and bind your data.

Sometimes, however, you may not have a column with a unique value.  As an example, I've created a database with three tables:  Publishers, Books and Magazines.  The Publishers table has a PublisherId and PublisherName.  The Book table has BookId, PublisherId and Title.  The Magazine table has MagazineId, PublisherId and Title.  This allows me to track all of my books and magazines and associate them with their publisher.

Sometimes I may want to see the entire list of publications with their publishers.  I've created a view that joins publishers and books and unions a query that joins publishers and magazines.  It adds an additional column called "PublicationType" so I can see what type of publication it is.  This creates a conundrum for me, however.  In my data I have a magazine that was formerly published by Publisher A but is now published by Publisher B.  Furthermore, Publisher A has two different magazines with the same title.  Now there is no way to guarantee uniqueness.

The SQL for my View looks like this:

CREATE VIEW [dbo].[VwPublications] AS

SELECT Publishers.PublisherName, 
	Books.Title,
	'Book' as PublicationType
FROM Books
	INNER JOIN Publishers ON Books.PublisherId = Publishers.PublisherId
UNION ALL
SELECT Publishers.PublisherName, 
	Magazines.Title,
	'Magazine' as PublicationType
FROM Magazines
	INNER JOIN Publishers ON Magazines.PublisherId = Publishers.PublisherId

And my result set looks like this:

In this particular case, EF generates the View in the EDMX and sets all columns as an entity key.  Querying the entire list of publications does return the correct results but that may not always be the case.  To replicate what happens, we remove EntityKey from the Title and PublicationType columns and just leave it on PublisherName.  When we then query the result via EF, we well get the correct number of records, but we will see data missing and data duplicated.

To fix this problem, we need to have a unique value in our view.  To do this, we will modify our view to have a Row_Number() generated column.  There are two issues with this.  First of all, Row_Number() Over expects an "Order By" clause.  This may not be desirable as we may not want the overhead of ordering our results and we want to order them ourselves in code.  A quick and easy way around this is to use Order By (SELECT 1) instead of a column name.  This will prevent the view from being ordered.  You can use any constant value you'd like (even Null) in place of 1.

The second issue we have is that our query has a union, which means that adding Row_Number() Over would need to be done in both queries and it will start over for the second query, resulting in 1, 2, 3, 4 for Books and 1, 2, 3, 4, 5, 6, 7 for Magazines.  Now we no longer have a unique value.  To remedy this, we will treat our View as a subquery and select from its result, adding the Row_Number to the outer query.

Our updated SQL now looks like this:

CREATE VIEW [dbo].[VwPublicationsWithIndex] AS

SELECT Row_Number() OVER(ORDER BY (SELECT 1)) AS RowNumber, * FROM
(
	SELECT Publishers.PublisherName, 
		Books.Title,
		'Book' as PublicationType
	FROM Books
		INNER JOIN Publishers ON Books.PublisherId = Publishers.PublisherId
	UNION ALL
	SELECT Publishers.PublisherName, 
		Magazines.Title,
		'Magazine' as PublicationType
	FROM Magazines
		INNER JOIN Publishers ON Magazines.PublisherId = Publishers.PublisherId
) AS NumberedResult

Now in our EDMX designer, we remove the Entity Key property from all of the columns except for RowNumber.  This allows Entity Framework to have a guaranteed unique column to tie the data back to your entities and your results will be correct.  A comparison of the two views in the designer looks like this:

As you can see, the RowNumber column has now been set as the Primary Key via "Entity Key".  There are a couple of additional things to keep in mind.  Annoyingly, any time you update your EDMX, the warning will still be displayed, even though you've specified the key.  Also, any time you're updating these particular views, you may have to manual re-apply the entity keys.

And finally, this problem serves as an important lesson.  While Warnings seem harmless, you should never just ignore them as they may cause big headaches down the road.

Many to Many Relationships in Entity Framework 7

Note:  The information in this post is way outdated.  For a more in depth started on EF Core and different relationship mappings please refer to my updated Deeper Dive Into EF Core post.

In the coming days I'll be introducing a new series of posts about working with MVC 6 and WebAPI in ASP .NET 5.  This will include demonstrations using Entity Framework 7 as I dive into it.  In the meantime, though, I wanted to demonstrate something that functions differently in EF 7 than in previous iterations: Many-to-Many relationships. More...

Identity Crisis - Identity vs SCOPE_IDENTITY In Sql Server

A common need when writing insert queries is to return the last Id that was generated when inserting your record.  There is more than one way to retrieve this value and it can come back to bite you if you're not careful.  In simple applications it's fine and simple to just append Select @@Identity to the end of your query.  If you manage the database and know the structure will never change this isn't a problem.  In larger scale applications, a DBA can make a simple change that can have major implications.  I'm going to demonstrate the problem and show you how to avoid this pitfall. More...