Entity Framework 7

Entity Framework 7 with a Class that Contains Collections

In order to explore entity framework a bit more I took my existing contact class and broke it down to the following.

public class Contact
{
    public int ContactId { get; set; }
    public string UserId { get; set; }
    [Required]
    [StringLength(200, MinimumLength = 0)]
    public string Name { get; set; }
    public List<ContactAddress> Addresses { get; set; }
    public List<ContactPhone> Phones { get; set; }
    public List<ContactEmailAddress> EmailAddresses { get; set; } 
}

The following is the new email address model from it you will get the idea of what the address and phone models contain.

public class ContactEmailAddress
{
    public int ContactId { get; set; }
    public int EmailId { get; set; }
    [EmailAddress]
    public string Email { get; set; }

    public Contact Contact { get; set; }
}

Next up was the DbContext which got a new DbSet, set up keys and relationships between the models. Again I am limiting the example to contact and email addresses but the same concepts apply to the other related models.

public class ContactsDbContext : DbContext
{
    private static bool _created;
    public DbSet<Contact> Contacts { get; set; }
    public DbSet<ContactEmailAddress> ContactEmailAddresses { get; set; }

    public ContactsDbContext()
    {
        if (!_created)
        {
            Database.AsRelational().ApplyMigrations();
            _created = true;
        }
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Contact>().Key(c => c.ContactId);

        builder.Entity<ContactEmailAddress>(entity =>
        {
            entity.Key(e => new { e.ContactId, e.EmailId });
            entity.Reference(e => e.Contact)
                  .InverseCollection(c => c.EmailAddresses)
                  .ForeignKey(e => e.ContactId);
        });
    }
}

In the above code the override of OnModelCreating establishes the connection between the models. InverseCollection establishes that there can be multiple email addresses for a single contact. I am not using it, but InverseReference would establish a one to one relationship.

After all the model changes are complete add a new migration from the command line using the following command. Then apply the migration by using the apply command or running the application if your application auto applies migrations.

dnx . ef migration add ContactsSplit --context ContactsDbContext

Here is an example query using this new class setup that include data from the contacts table and joins the email address table to get the related email addresses.

var contacts = from c in _db.Contacts
                         .Include(c => c.EmailAddresses)
               where c.UserId == User.GetUserId()
               select c;

The include statement above is what make entity framework eagerly load the data from the email address table. The default behavior would not load the email addresses. Also note that include statements can be chained as needed.

The following query is the same as above without eagerly loading the contact’s email addresses.

var contacts = from c in _db.Contacts
               where c.UserId == User.GetUserId()
               select c;

At this point the EmailAddresses property of the returned contacts would be null. To load the email addresses for a particular contact the following can be used.

_db.ContactEmailAddresses.Where(e => e.ContactId == contact.ContactId).Load();

This post on stackoverflow has a great set of tips related to collections with entity framework 7.

Entity Framework 7 with a Class that Contains Collections Read More »

ASP.NET 5 Getting and Storing User Identity

July 25, 2015 / ASP.NET 5, Entity Framework 7 / ASP.NET 5, Entity Framework 7, MVC 6

My contacts application has login options but does not currently tie data to a specific user. In this post I am going to fix that problem. Add a new user ID property to the Contact class for storing the user’s ID.

public string UserId { get; set; }

The change to contact model needs to be applied to the database. To do this use the following dnx command to add a new entity framework migration.

dnx . ef migration add AddContactUser --context ContactsDbContext

At this point a run of the application will apply the migration to the database. This is assuming use of Database.AsRelational().ApplyMigrations() in the constructor of the appropriate data context, if not then dnx can be used to apply the migration.

The ContactsContoller now needs to require a logged in user as well as get the ID for the logged in user. The following two using statements are need to be added to support these operations.

using System.Security.Claims;
using Microsoft.AspNet.Authorization;

Microsoft.AspNet.Authorization provides access to the authorize attribute which is used to require an authorized used. The authorize attribute can be used a the class or on individual functions. This example is at the class level.

[Authorize]
public class ContactsController : Controller

System.Security.Claims provides an extension method on the controller’s User property to get the current user’s ID. The following is the code from the index action for the ContactsController with the added filter for user ID on the contacts query.

public IActionResult Index(string filter)
{
    var contacts = from c in _db.Contacts
                   where c.UserId == User.GetUserId()
                   select c;

    if (!string.IsNullOrEmpty(filter))
    {
        contacts = contacts.Where(c => c.Name.Contains(filter) ||
                                       c.Address.Contains(filter) ||
                                       c.City.Contains(filter) ||
                                       c.Email.Contains(filter) ||
                                       c.Phone.Contains(filter) ||
                                       c.State.Contains(filter) ||
                                       c.ZipCode.Contains(filter));
    }

    if (Request?.Headers != null && 
        Request.Headers["X-Requested-With"] == "XMLHttpRequest")
    {
        return PartialView("_ContactList",contacts);
    }

    return View(contacts);
}

A similar change needs to be made on all the actions in the ContactsController. For example here is the user ID being used as part of the create action.

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create(Contact contact)
{
    if (ModelState.IsValid)
    {
        contact.UserId = User.GetUserId();
        _db.Contacts.Add(contact);
        _db.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(contact);
}

This method works fine but I am not sure if only using the User ID on the server-side is best practice or not. The other option would be to send the user ID to the client as a hidden field which would get returned when the user posted changes. It seems that forcing the User ID on the contact model on the server-side would prevent it from be tampered with, but this is a topic that I need to research further.

ASP.NET 5 Getting and Storing User Identity Read More »

Starting Over with Entity Framework 7

July 18, 2015 / ASP.NET 5, Entity Framework 7 / ASP.NET 5, Entity Framework 7, Migrations

Last week as part of a migration from ASP.NET 5 beta 4 to beta 5 I deleted all the entity framework migrations for a project. This post is going to walk through the process deleting the existing database, creating new migrations and applying the new migrations.

The first step is to delete the existing database associated with the project. To do this from within Visual Studio 2015 open the SQL Server Object Explorer. Click on the Add SQL Server button (second button).

The add button launches the Connect to Server dialog. If using the default setup with SQL Server Express the settings below should work. After all the relevant information has been entered click connect.

Right click on the database for the application and click delete.

Another option would be to change the database name in the connection string which would also trigger entity framework to create a new database.

The next step is to add migrations for each DbContext in your project. The following two commands, run from the command prompt, will add migrations for the two contexts associated with my application.

dnx . ef migration add ApplicationInit --context ApplicationDbContext
dnx . ef migration add ContactsInit --context ContactsDbContext

The following is a repeat, but it is important part of the setup since in it is how this application applies migrations. The constructors of the DbContext classes apply migrations as demonstrated in the following code.

public class ContactsDbContext : DbContext
{
    private static bool _created;
    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext()
    {
        if (!_created)
        {
            Database.AsRelational().ApplyMigrations();
            _created = true;
        }
    }
}

After the first run of the application a new database will be created and you will be ready to go with a fresh database.

Starting Over with Entity Framework 7 Read More »

ASP.NET 5 Web Site to Azure

July 4, 2015 / ASP.NET 5, Azure, Entity Framework 7 / ASP.NET 5, Azure, Entity Framework 7, Migrations, User Secrets, Visual Studio

At the point the basics of my ASP.NET 4 contacts application have been moved to the ASP.NET 5. This is never going to be a production application, but I want it run it on a remote server just to prove it works. I decided to publish to Microsoft Azure which is Microsoft’s cloud offering. The process was a lot more challenging that I had expected, but as with all the rough spots I have hit with ASP.NET 5 I am sure the path will be made smooth for the final release.

Publishing to Azure from Visual Studio 2015 RC

To get started right click on the project to be published and select the Publish.

This will load the Publish Web dialog. On the Profile tab select the Microsoft Azure Web Apps option.

This will show the Select Existing Web App dialog. Click the New button to add a new Web App.

The new button show the Create Web App on Microsoft Azure. This dialog has a bit more to it. Web App name sets the url of the app as well as the app name with Azure. For region I just chose the closest data center. My application uses a database and I don’t have an existing database server in Azure so I chose Create new server for Database server selection. Database username and password are self explanatory. With all the options filled in click Create.

After the creation process, which creates all the infrastructure need for the app in Azure, is done Visual Studio returns to the Publish Web dialog. The following is the Connection tab. The fields are editable, but are auto filled from the creation process. In case changes are need use the provided Validate Connection button to verify Visual Studio is still able to communicate with Azure.

The Settings tab allows selection of Release or Debug configurations as well as the target DNX version.

The last tab on the Publish Web dialog is Preview. It is not overly useful on a first publish since all the file for the project needed to be push, but on subsequent publishes it would be useful to verify nothing unexpected is being pushed.

Pushing the Publish button on the Publish Web dialog push the files to Azure and opens a browser with the newly published web app.

Issues

After a few seconds I was greeted with a HTTP 500 Internal Server Error instead of my web app. I spent a lot of time on the Azure Portal trying to find my issue. I created a new project and published it without issue which means it is a problem with my app and not the publish process. I spent a lot of time digging and Googling my issues, but thankfully ended up with answers.

App Configuration

My first issues were the result of not setting up the configuration options for my user secrets. As I posted last week this was the main worry I had with user secrets. You would think with my concerns that would be the first thing I checked but it actually took me awhile to get around to checking my user secrets. Just to be clear I am not against user secrets I think they are an awesome feature I am just not used to dealing with this side of configuration. The steps to make the proper configuration for user secrets follow.

From the Azure Portal click Browse everything to get a list of all resources.

From All resources click on the Web App that needs configuration. If you are paying close attention you will notice the name of my web app is different than the publication settings above. This is the result of one of my tries to get the site running before discovering the problem was configuration.

Selecting a web app cause the details page to load. On the details page select Settings.

From the settings details page click Application Settings which will load the Web app settings page.

In Web app settings scroll down to the App settings section. In my case I was missing the Authentication:Google:ClientId and Authentication:Google:ClientSecret settings used for OAuth with Google. Also make note of the Connection strings section as this is the section where the connection string to the database needs to be entered.

With all the above changes I was finally able to get the site to load.

Database/Entity Framework

As soon as I click on the contact list section of my app I got another HTTP 500 Internal Server Error. This happens to be the first time the app hits the database. The issue this time is that the DefaultConnection string that needs set so the app can connect to the database.

First step to fix this issue is to go back to the All resources on the Azure Portal and select the database that goes with the web app, aspnetcontacts_db in this example.

This will load the SQL Database detail. Under Connection string is a link for Show database connection strings. Clicking this will load a page with a list of connection strings for this database. Copy the appropriate string and replace the dummy password with the real one. Take that connection string with the password enter and put it in your web app’s DefaultConnection. With the DefaultConnection set the 500 error went away.
The next time I ran I got a stack trace with a message that migrations needed to be run. In order to get migrations to run I made a few changes to my project.

The first change was in the ConfigureServices function of Startup. I added the setup for the ContactsDbContext using the DefaultConnection string. In the ContactsDbContext I removed the OnConfiguring function which is where the connection string for the ContactsDbContext had been set before.

// Add EF services to the services container.
services.AddEntityFramework()
   .AddSqlServer()
   .AddDbContext<ApplicationDbContext>(options =>
       options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]))
   .AddDbContext<ContactsDbContext>(options =>
       options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

In order to actually run migrations for the ContactsDbContext I added a constructor with a call to Database.AsRelational().ApplyMigrations() which will ensure anytime the DbContext is constructed that the latest migrations will be applied. The following is the full ContactsDbContext class after the changes.

public class ContactsDbContext : DbContext
{
    private static bool _created;
    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext()
    {
        if (!_created)
        {
            Database.AsRelational().ApplyMigrations();
            _created = true;
        }
    }
}

The last project change was to the ContactsController to allow ASP.NET to inject the ContactsDbContext instead of creating the context with in the controller.

private readonly ContactsDbContext _db;

public ContactsController(ContactsDbContext dbContext)
{
    _db = dbContext;
}

The change made above to the ConfigureServices function in the Startup class is how ASP.NET knows what to inject into the ContactsController. Build in dependence injection is one of the new features of ASP.NET 5.

After all the above changes I published to Azure and tried to access the contact list again. This resulted in a different error and a stack trace. The issue this round turned out to be that the default SQL Server created by Azure does not support the way that Entity Framework 7 is auto incrementing the ID column on contacts table. Thankfully there is an updated version of SQL available and the default server just need to be upgraded.

Back on the detail page for the SQL database there is a Server version listed as V2. Click the V2.

This will load the Latest SQL database update page. Click Upgrade This Server.

Currently the SQL Server created by the Visual Studio 2015 RC is in the Web tier which does not support the latest version of SQL which resulted in the warning below.
Click on your database name to load the Recommended pricing tier page. Since this is a new database Azure does not have enough information to recommend a tier and defaults to S0. At the very bottom of the page there is a big blue S0 link. Click it to change pricing tiers.

The Choose your pricing tier page will load. The current setting for the server is Web which shows as a retired. I chose the Basic tier but anything that is not marked as retired should support the newer version of SQL Server. After clicking on the option you want click the Select button at the bottom of the page.

Now that the new pricing tier has been selected go back to the server details and click the V2 link under Server version. This round you will get a big warning. Under the warning enter the name of your server and click OK to perform the upgrade.

The upgrade process took less than 20 minutes for me. After the processes was complete the app worked as expected.

ASP.NET 5 Web Site to Azure Read More »

New Model Using Entity Framework 7 and ASP.NET 5

June 6, 2015 / ASP.NET 5, Entity Framework 7 / ASP.NET 5, Entity Framework 7

Last week I created a very basic ASP.NET 5 application based on a Visual Studio template with no changes. I am now going to take that project and add in the ability to do CRUD operations for the following Contact model.

public class Contact
{
    public int Id { get; set; }
    [Required]
    [StringLength(200)]
    public string Name { get; set; }
    [StringLength(400)]
    public string Address { get; set; }
    [StringLength(100)]
    public string City { get; set; }
    [StringLength(2)]
    public string State { get; set; }
    [Display(Name = "Zip Code")]
    [StringLength(10)]
    public string ZipCode { get; set; }
    [StringLength(100)]
    public string Country { get; set; }
    [StringLength(40)]
    public string Phone { get; set; }
    [EmailAddress]
    public string Email { get; set; }

}

I am using Entity Framework 7 to manage database interaction. Entity Framework 7 is a full rewrite just like ASP.NET 5. To get access to contacts a DbContext is need just as in EF 6. The OnConfiguring override is new in EF 7 and was needed for me to get the controller generation process to work which is covered below. Optimally this connection string would be in a configuration file, but that is out of the scope of this post.

public class ContactsDbContext : DbContext
{
    public DbSet Contacts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;" +
                                    "Database=aspnet5-Contacts;" +
                                    "Trusted_Connection=True;" +
                                    "MultipleActiveResultSets=true");
    }
}

At this point I was ready to add a migration for the new model. I ran into a few issues getting this process working. I was unable to get migrations to work using the package manager console and ended up having to use a console. This should all be smoothed out by the time final release is done.

I ran all the command while in the same folder as the project.json file. The commands are run by the .Net Execution Environment or as the exe is name dnx. Below is a screenshot of the console after running the migration add command.

dnx . ef migration add ContactsInitial -c ContactsDbContext

The next step was to apply the migration to get the database setup which is also run in the console.

dnx . ef migration appy ContactsInitial -c ContactsDbContext

ASP.NET 4 had great support for creating controllers and views from a model. That functionality is currently missing from the UI of Visual Studio. This is another thing that I am sure will be added by the final release. But for now back to the console with the following command to create a controller and associated views.

dnx . gen controller -name ContactsController --dataContext ContactsDbContext --model Contact

Now we need some way to access the new views. I added a Contacts item to the nav bar. This is done by editing _Layout.cshtml in Views/Shared folder and adding the following line to the ul with a class of “nav navbar-nav”.

<li><a asp-controller="Contacts" asp-action="Index">Contacts</a></li>

At this point contacts should be useable. What I discovered when I clicked on my Contacts link was a useable by very ugly page. The generated views are not using the shared layout by default. To fix this open all the views and remove the following from the top of the page.

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Create</title>
</head>
<body>

And the following from the bottom.

</body>
</html>

With the above removed the views will now use _Layout.cshtml and match the rest of the site. At this point the generated items pretty much match the scaffolding option from ASP.NET 4.

New Model Using Entity Framework 7 and ASP.NET 5 Read More »