db.Database.Migrate(); doesn't Work on EF Core 3.1.0 for UWP Project

[shakir820]

I have been using EF Core 2.2.6 and EF Core Tool 2.2.6 in my UWP project. Now I want to use version 3.1.0. But I cannot migrate my SQLite db at app startup. I get error at db.Database.Migrate();
Can anyone help me with how to use EF Core v3.1.0 or 3.1.1 on the UWP project? I am ready to provide you more info if you need it.

My project details:

MusicPlayer10 (UWP project, version 16299)
DataModel (.NET Standard v2.0, for SQLite model)
MigrationStartup (.NET Core v2.2)

Further technical details

EF Core version: 3.1.0
Database provider: SQLite
Target framework: UWP 16299
Operating system: Windows 10 (OS Build 18363.XX)
IDE: Visual Studio 2019

[bricelam]

What error are you seeing?

[vichuviswan]

@bricelam
I see similar issue on my UWP application too. Until 2.2.6, it works fine, but from 3.0.0 onwards, it fails. The only difference between what is reported vs what I see on my side is, for me, it fails to open the database.

It throws an error "SQLite Error 14: Unable to open database file". Please find below the exact error.

Not sure if its the same error that @shakir820 is seeing on his side.

Any assistance is really appreciated. And if there is any additional details, required, please let me know that too. Thanks!!

[shakir820]

Let me clear you the details. If I use this...........

I get this error.

But if I use this......................................

I don't get any error.

But I used to follow the 1st one approach for migration in v2.2.6. Now seems like I need to explicitly define the location in v3.1.1 which is not intelligent (I think).

[bricelam]

See ericsink/SQLitePCL.raw#270

[shakir820]

@bricelam , Thank you.

[shakir820]

Issue solved. Closing it.

[shakir820]

Why this is reponed?

[ajcvickers]

@shakir820 I want to discuss documenting it with the team.

[shakir820]

Ok ok. No problem

[shakir820]

Guys, I have discovered a new problem. I used Windows.Foundation.UniversalApiContact assembly in class library project (.NET Standard 2.0.x). to solve my problem.

After that, I had no problem running this code.............
db.Database.Migrate();

But now I cannot migrate using Package Manager Console. It throws an exception

Can you help me?? @bricelam

[bricelam]

This might work:

string dbDir;
try
{
    dbDir = ApplicationData.Current.LocalFolder.Path;
}
catch
{
    dbDir = AppContext.BaseDirectory;
}

var dbPath = Path.Combine(dbDir, "YourDbName.db");

[shakir820]

Thanks. But I cannot use ApplicationData in .NET Standard project. Because I have separated the EF Core model from my main project. I just followed the Microsoft provided sample.

Even if I want to use ApplicationData in .NET Standard I need to add a reference of Windows.Foundation.UniversalApi to the project. And then I get this error.

[bricelam]

You have two options:

• Use reflection. We has some code to do this in version 1.x.
• Flow it in from the UWP project--make the .NET Standard project just take a string

[ajcvickers]

Note from team meeting: move this to the docs repo.

[shakir820]

Note from team meeting: move this to the docs repo.

But why? Shouldn't the Microsoft fix this issue?

[bricelam]

Agreed; not being able to write to the current working directory is a pretty bad experience. Please submit UWP feedback here.

[shakir820]

Agreed; not being able to write to the current working directory is a pretty bad experience. Please submit UWP feedback here.

This is not UWP problem. It's all about the EF Core problem. I cannot use ApplicationData in .NET Standard project if I use EF core 3.1.x and EF Core cannot migrate without explicit database file location.

Seems like EF Core and UWP are fighting with each other.

[ajcvickers]

@shakir820 Do you have a proposal for what EF should do differently here?

[shakir820]

@ajcvickers , Yes, I want full support for the UWP apps. And I want db.Database.Migrate() to work properly without explicitly defining the database location just like v 2.6.x.

[ajcvickers]

@shakir820 Let me be a little bit more explicit in my question. The background here is:

• EF Core and SQLite are not tightly coupled to UWP. UWP is just one of multiple platforms that EF Core and SQLite could run on.
• All other platforms that I'm aware of setup the current working directory as somewhere the application is expected to be able to write to and read from. UWP instead requires that some other location to be used, and that requires non-trivial UWP-specific code.

So this means that either:

• EF Core (or SQLite) needs to have special code to support UWP because it behaves differently from other platforms. This is doable, but it adds unwanted coupling from EF/SQlite to UWP.
• Or UWP needs to align with other platforms on what it provides as the current working directory.

So, asking my question again, is it your opinion that EF should take this dependency on UWP? Or do you have ideas for some other way we could make this work in EF?

[shakir820]

@ajcvickers , Thanks for the clarification. I am taking some days to think about it. I will inform you very soon. I am trying to find out the appropriate way to run db.Database.Migrate() with the existing features of the EF Core.

[ajcvickers]

We discussed this again and came to the following conclusions:

• It doesn't seem like UWP is going to make the current directory usable
• We can make this work from the EF side
• Therefore, even though we don't want UWP-specific code in EF Core, in this case we will put it in order to let people get past at least this UWP limitation without major hassle.

We will target this for a 3.1.x patch release, although this will need approval.

[hjohnson12]

You have two options:

• Use reflection. We has some code to do this in version 1.x.
• Flow it in from the UWP project--make the .NET Standard project just take a string

Hi @bricelam and @ajcvickers , hope you're both doing well!

I actually just ran into this issue on my UWP Kanban application after adding SQLite migration features. I'm unfamiliar with reflection, but are you able to provide an example of how I'm able to use your code in that snippet to workaround this, if possible?

In my commit above, I just changed it to use the full path but ended up running into the process identity issue as well. Worst-case I can just revert the package updates but hoping that's not the case lol Thanks!

Best Regards,
Hunter

[ajcvickers]

@hjohnson12 We will look into it.

[sjb-sjb]

I do not have a good grip on the technicalities here but as a comment it would be really useful if we could pick a directory to place the user database into. See aspnet/Microsoft.Data.Sqlite#287 for some earlier discussion on this.

I had the same problem as others described which was that when upgrading to EFCore 3, I received SQLite error 14, could not open file. Noting however that I was not using migrations, just opening the file.

[sjb-sjb]

@bricelam Pending the release of this fix, where do I put these two lines in my EF code? I tried putting them in OnConfiguring and in OnModelCreating and I got a null reference exception both times.

 SQLitePCL.raw.sqlite3_win32_set_directory(/*data directory type*/1, Windows.Storage.ApplicationData.Current.LocalFolder.Path);
 SQLitePCL.raw.sqlite3_win32_set_directory(/*temp directory type*/2, Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);

… and, thanks !!!

[bricelam]

Ah, you’ll need to call SQLitePCL.Batteries_V2.Init() first. During app start is probably the best place, but anywhere before you open a connection will do

[sjb-sjb]

Confusingly, if you do set the directories as discussed and then call e.g. EnsureCreatedAsync then Error 14 will still be thrown; but it will be caught before EnsureCreatedAsync returns. In other words, after applying the workaround that was discovered by breaking on the exception, you then have to set the debugger to not break on the exception (or let it break and you can then continue execution).

[ghost]

For anyone else that keeps their DbContext in a separate .NET Standard class library within a UWP solution, you will have to use:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    string dbPath =  Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), 
"myDatabaseName.db");
    optionsBuilder.UseSqlite($"Data Source={dbPath}");
}

[sjb-sjb]

@ajcvickers any chance that the EF team can encourage the sqlite people to solve the directory issue linked above (aspnet/Microsoft.Data.Sqlite#287) ?

[shakir820]

@kayarray , Thanks for your help. It worked. I think issue is fixed. But if you guys want you can add a special feature for UWP to EFC.

[hjohnson12]

@shakir820 Were you able to create the migration afterwards? I gave it a try out and was unable to still 🤔

[ghost]

@hjohnson12 Do you have the DbContext in a .NET Standard class library? What error did you get?

I have been running migrations directly from the .NET Standard class library by making the following changes to the class library csproj file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <!-- USE FOR NORMAL RELEASE -->
    <TargetFramework>netstandard2.0</TargetFramework>
    <!-- USE BEFORE ISSUING ADD-MIGRATION IN PACKAGE MANAGER CONSOLE. MAKE SURE 'Default Project:' drop-down is set to this class library. -->
    <!--<TargetFrameworks>netcoreapp2.2;netstandard2.0</TargetFrameworks>-->

I then use this: Add-Migration -Project [ClassLibraryName] -Startup [ClassLibraryName] Initial

It all works fine for me.

[Noemata]

@kayarray, if you do the migration from the Nuget console with just the netstandard2.0 target, you get this:

Startup project 'EFGetStarted.Model' targets framework '.NETStandard'. There is no runtime associated with this framework, and projects targeting it cannot be executed directly. To use the Entity Framework Core Package Manager Console Tools with this project, add an executable project targeting .NET Framework or .NET Core that references this project, and set it as the startup project; or, update this project to cross-target .NET Framework or .NET Core. For more information on using the EF Core Tools with .NET Standard projects, see https://go.microsoft.com/fwlink/?linkid=2034705

With the 3.1.2 nuget package, nuget console operations are working as previously documented for UWP, so this issue can be closed. Thank you.

Below is the code for a working UWP console app that mirrors the current docs:

// Model.cs
using System;
using System.IO;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace EFGetStarted
{
    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        static string dbPath = null;
        public static string DbPath
        {
            get => dbPath;
            set => dbPath = value;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            if (DbPath == null)
                options.UseSqlite("Filename=blogging.db");
            else
                options.UseSqlite($"Filename={Path.Combine(DbPath, "blogging.db")}");
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        public List<Post> Posts { get; } = new List<Post>();
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public Blog Blog { get; set; }
    }
}

// Program.cs
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using Windows.Storage;

namespace EFGetStarted
{
    class Program
    {
        static void Main()
        {
            BloggingContext.DbPath = ApplicationData.Current.LocalFolder.Path;

            using (var db = new BloggingContext())
            {
                db.Database.Migrate();

                // Create
                Console.WriteLine("Inserting a new blog");
                db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
                db.SaveChanges();

                // Read
                Console.WriteLine("Querying for a blog");
                var blog = db.Blogs
                    .OrderBy(b => b.BlogId)
                    .First();

                // Update
                Console.WriteLine("Updating the blog and adding a post");
                blog.Url = "https://devblogs.microsoft.com/dotnet";
                blog.Posts.Add(
                    new Post
                    {
                        Title = "Hello World",
                        Content = "I wrote an app using EF Core!"
                    });
                db.SaveChanges();

                // Delete
                Console.WriteLine("Delete the blog");
                db.Remove(blog);
                db.SaveChanges();
            }
        }
    }
}

[ghost]

@Noemata wrote:

@kayarray, if you do the migration from the Nuget console with just the netstandard2.0 target, you get this:

Startup project 'EFGetStarted.Model' targets framework '.NETStandard'. There is no runtime >associated with this framework, and projects targeting it cannot be executed directly. To use the >Entity Framework Core Package Manager Console Tools with this project, add an executable project >targeting .NET Framework or .NET Core that references this project, and set it as the startup >project; or, update this project to cross-target .NET Framework or .NET Core. 

You will get that if try to run a migration from the PMC without changing the .NET Standard 2.0 library csproj file as I showed above (see line). There is no need for a separate console app in the solution.

I have a normal UWP project with the dbContext in a .NET Standard 2.0 library and it has always worked fine for me. Change the csproj as I outlined, and then run the Add-Migration command as shown.

Add-Migration -Project [ClassLibraryName] -Startup [ClassLibraryName] Initial

If you don't set 'Default project' to the class library, and don't change the csproj TargetFramework, you will get the error message you posted. Setting both -Project and -Startup options to the library eliminates the need to change the 'Default project' drop-down.

[Noemata]

@kayarray, using VS2019 with (dotnet --list-sdks) SDK 3.1.200 installed, having just:

    <TargetFramework>netstandard2.0</TargetFramework>

Results in this error:

Startup project 'EFGetStarted.Model' targets framework '.NETStandard'.
There is no runtime associated with this framework,
and projects targeting it cannot be executed directly.
To use the Entity Framework Core Package Manager Console Tools with this project,
add an executable project targeting .NET Framework or .NET Core
that references this project, and set it as the startup project;
or, update this project to cross-target .NET Framework or .NET Core.
For more information on using the EF Core Tools with .NET Standard projects,
see https://go.microsoft.com/fwlink/?linkid=2034705

However with the latest nuget package (3.1.2), provided you unload the UWP sister project during this operation, adding migrations from the PMC is working again. You can also add migrations from the command line within the directory of the Model's project.

Creating your DB from the command line can also be done, provided you incorporate the resulting DB into your solution and adjust your DbPath as shown in the above console app, to be set as follows:

BloggingContext.DbPath = AppContext.BaseDirectory;

If you do create your DB from the command line, calling db.Database.Migrate(); will result in a different behavior, so you'll have to adjust your design.

I'm sticking with the originally described approach. Would be nice if someone updated the docs for UWP to incorporate the new usage requirements.

[Noemata]

Note, things behave differently once you've done your first migration from the PMC. It seems you do not need to unload the UWP project after the first migration succeeds. So the unload step is only required when you first start your UWP app solution. Though you still have to set the Model project as the start up solution before doing the migration step.

[ghost]

Note, things behave differently once you've done your first migration from the PMC. It seems you do not need to unload the UWP project after the first migration succeeds. So the unload step is only required when you first start your UWP app solution. Though you still have to set the Model project as the start up solution before doing the migration step.

There are only 3 steps required to successfully run migrations on a DbContext that is in a .NET Standard 2.0 library as part of a UWP solution:

1. Add <TargetFrameworks>netcoreapp2.2;netstandard2.0</TargetFrameworks> to the library .csproj file.
2. Comment out the existing <TargetFramework>netstandard2.0</TargetFramework>
3. In PMC, issue this command:
   Add-Migration -Project [ClassLibraryName] -Startup [ClassLibraryName] Initial

Step 1 is critical. That netcoreapp2.2 reference is what pulls in the SDK required to run the Add-Migration command as described. Only one of those TargetFramework lines can be active at any given time, which is why I included the notes about commenting one of them out,
(CTRL+E,(C or U) with caret on the line will toggle an XML comment in the library project csproj file.)

Executing the Add-Migration command with both options supplied will eliminate any need to change the Default-dropdown in PMC, or change the Startup project in the UWP solution.

After running the migration, go back into the library csproj file and comment out the TargetFramworks line that was added, and uncomment the previously commented TargetFramework line. We're just switching between the use of one vs the other here.

The solution will then build fine, without any reference to a netcoreapp SDK in it.

I have never had a problem doing it this way. That is still the case with 3.1.2 - I was only providing a solution here for the breaking change in the path used by the sqlite3 dll. (How to get the correct full path in a netstandard 2.0 library by using System.Environment properties.)

[Noemata]

Thanks for the clarification @kayarray, I hadn't realized "-Project [ClassLibraryName]" resolves the need for unloading the UWP project. I had tried that at some point, guess things were still in flux. Nice.

I haven't had to make any special changes when selecting "Class Library (.Net Standard)" for the .Net Starndard part of the solution. Leaving <TargetFrameworks>netcoreapp2.0;netstandard2.0</TargetFrameworks> seems to work as is.

[sjb-sjb]

Noting that neither -Project nor -Startup is described in https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/?tabs=vs
Should there be a review / update of that page?