Entity Framework Core with Xamarin.Forms

With the release of Entity Framework Core 2.0, you can now run EF with a SQLite database on iOS, Android and UWP. For those coming from server or desktop development, this will mean a very easy transition to mobile database development.

Project Setup

You must have .NET Core SDK 2.0 installed, and your class library must be set to use .NET Standard 2.0.

Install Microsoft.EntityFrameworkCore.Sqlite in every project, the traditional and the class library. It will install all other required dependencies, as appropriate. You are now ready to get coding. I have an example repo, EFXamarin on GitHub if you want to see a completed solution.

Note: Do NOT add the SQLite extension in UWP, this is not needed with EF Core and will cause a conflict, if added.

Converting PCL to .NET Standard Library

If you have a PCL, a quick way to convert it to a .NET Standard Library, is to edit the csproj of this PCL. Delete everything inside, and replace it with this.

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <TargetFramework>netstandard1.3</TargetFramework>
        <PackageTargetFallback>$(PackageTargetFallback);portable-win+net45+wp8+win81+wpa8</PackageTargetFallback>
    </PropertyGroup>
</Project>

Now reload your project, and add back in your Nuget packages and references. It’s easier to wipe them all out and add them again, rather than manually doing it.

Native Project Setup

All you need to do in each platform, is get the path to the SQLite database. This is slightly different in each platform. You will need to pass this value into the class library, and that approach is up to your existing architecture. For simplicity sake, I will just pass this through the constructor of the App.cs.

// Android
var dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "exrin.db");

// UWP
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "exrin.db");

// iOS
var dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "..", "Library", "exrin.db");


Additional iOS Setup

iOS complains on startup, if you don’t add the following line. I add it just before the Xamarin.Forms.Init(), in the AppDelegate.cs

SQLitePCL.Batteries.Init();

Database Setup

First you want to create the Tables, for your database. All you need to do, is create a class, and if you want a primary key, or other attributes, add them as appropriate.

public class Blog
{
    [Key]
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }

    public override string ToString()
    {
        return Url;
    }
}

Now, create the DbContext.

public class DatabaseContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    private string _databasePath;

    public DatabaseContext(string databasePath)
    {
        _databasePath = databasePath;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite($"Filename={_databasePath}");
    }
}

Adding and Retrieving Data

Next, all we need to do is EnsureCreated is called, to initialize the database, then add and retrieve data, just as you normally would with Entity Framework.

public App(string dbPath)
{
    List<Blog> itemSource;

    // Create Database & Tables
    using (var db = new DatabaseContext(dbPath))
    {
        // Ensure database is created
        db.Database.EnsureCreated();

        // Insert Data
        db.Add(new Blog() { BlogId = 1, Rating = 5, Url = "https://exrin.net" });
        db.Add(new Blog() { BlogId = 2, Rating = 5, Url = "https://xamarinhelp.com" });
        db.Add(new Blog() { BlogId = 3, Rating = 5, Url = "https://azuremobilehelp.com" });
        db.SaveChanges();

        // Retreive Data
        itemSource = db.Blogs.ToList();
    }

    // Show Data
    MainPage = new ContentPage()
    {
        Content = new ListView()
        {
            ItemsSource = itemSource
        }
    };
}

And then if you run the project on Android, UWP or iOS, will you see.

Note: EFCore 1.0 with Mono 5.0+ will work on all platforms. EFCore 2.0 with Mono 5.0+ will work on all platforms except UWP . But only because support for .NET Standard 2.0 in UWP is still in the works, hence you must wait for that support, and then UWP will work with EFCore 2.0.

androidentityframework uwpentityframework

 

If you run any Mono Version lower than 5.0, you will get the following error, in iOS, if using an older version of EF Core. To use EF Core 2.0 you need at least Mono 5.2, hence you need to ensure you have installed Visual Studio 15.3 or higher.

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NotImplementedException: Interpreter of ref types
 at Microsoft.Scripting.Interpreter.LightCompiler.CompileMethodCallExpression (System.Linq.Expressions.Expression expr) [0x00089] in /Library/Frameworks/Xamarin.iOS.framework/Versions/10.4.0.114/src/mono/mcs/class/dlr/Runtime/Microsoft.Dynamic/Interpreter/LightCompiler.cs:1283 
 at Microsoft.Scripting.Interpreter.LightCompiler.CompileNoLabelPush (System.Linq.Expressions.Expression expr) [0x001ba] in /Library/Frameworks/Xamarin.iOS.framework/Versions/10.4.0.114/src/mono/mcs/class/dlr/Runtime/Microsoft.Dynamic/Interpreter/LightCompiler.cs:1642 
 at Microsoft.Scripting.Interpreter.LightCompiler.Compile (System.Linq.Expressions.Expression expr) [0x00008] in /Library/Fra
meworks/Xamarin.iOS.framework/Versions/10.4.0.114/src/mono/mcs/class/dlr/Runtime/Microsoft.Dynamic/Interpreter/LightCompiler.cs:1729 

etc...


XAMARIN.FORMS MONTHLY NEWSLETTER

JOIN 1,000+ SUBSCRIBERS

  1. Don't miss out on updates
  2. The latest info from this site, Xamarin and the community
  3. Unsubscribe at any time*

* We use MailChimp, with double opt-in, and instant unsubscribe

Microsoft MVP | Xamarin MVP | Xamarin Certified Developer | Exrin MVVM Framework | Xamarin Forms Developer | Melbourne, Australia

36 Comments

  1. Christoph Nienaber

    Cool stuff!
    Would love to see an update on this topic when iOS is supported. People with MVC background are familiar with EF and will feel right at home, when it just works.

    Can you do a comparison to using SQLite (only)? Do you think you will use EF Core only when the support is there?

    Greetings

    1. Adam Pedley

      When using SQLite, you would normally use the sqlite-net-pcl nuget package to communicate with a SQLite db. However, yes, when EFCore supports iOS, I will most likely move to EF Core only.

      Would much prefer a Microsoft open-source supported framework. I used EF lots with web development, and it would be great for people coming from that background, as you mentioned.

  2. Plamen Yovchev

    Hi Adam,

    Is it possible and so easy to work with database first approach. I have existing sqlite db which I want to use and not to write my own classes and to create the tables again. Is it possible to load the tables like in asp.net mvc like entities ?

    1. Adam Pedley

      If the database and table already exists, it won’t overwrite it. Thats what the EnsureCreated does. The only difference is you will have to have your db in your app, and ensure its outputted to the mobile device in the correct location when they first run the app.

      1. Plamen Yovchev

        So, I have to write classes for the existing tables and use the EnsureCreated method and etc. to be able to use them like “newly created entities”, correct ?

        P.S. sorry for the duplicaton.

        1. Adam Pedley

          Yes, just use it as you normally would with Code First. You could technically remove EnsureCreated I think because it will already be there, but it leaves the creation of the db entirely up to you. I have tried leaving out the EnsureCreated before, so give it a test first, it might also do other things in the background I am not aware of.

    1. Adam Pedley

      Unfortunately, I know nothing further than what is publicly stated. I would be looking out for Xamarin Cycle 10 with Mono 5.0 which will include the CoreFX upgrade, this may resolve it but its just speculation, it could require a completely separate fix.

  3. TimK

    Thanks for the excellent post, Adam. In our Xamarin.Forms PCL project, we are using SQLite local storage and connecting to a PostgreSQL external database from which to pull data into local storage. What would your advice be in terms of navigating the flow from the external database to the local one? At this point it seems best to use sqlite-net-pcl for the local db. Would you use a different Nuget package to connect on the PostgreSQL side and then translate the data to be sqlite-net-pcl compatible so as to store it locally?

    1. Adam Pedley

      There would be nothing avail that does this automatically. If you are connecting directly to the PostgreSQL over the internet, I would suggest you instead build an API to service the requests, you shouldn’t directly expose your DB to mobile clients.

      From here you would have to develop your own Syncing process. You might want to look at Azure Mobile Client, which includes syncing, but I am not about its capability to connect it up to an existing external source.

  4. Pete Suffolk

    Hi Adam,

    Having used code first for years I am keen to continue the methodology with Xamarin Forms for Android.

    I have downloaded your exampl and it works great.

    I have used your example to try to work out how to use EF in a XAML based application but I have failed, miserably!

    Have you an example on Entit Framework with a Xaml based application? I only need Android if it helps.

      1. Pete Suffolk

        Hi Adam,

        Thank you for your reply.

        I have downloaded your MVVMLight example however, I cannot see any references to Entity Framework in it. Furthermore I couldn’t get it to build as there are a couple of dll’s missing: APiRepositorry and DataService.

        I am still interested in a simple XAML based Entity Framework example though.

        1. Adam Pedley

          Hi Pete

          I don’t have any examples of EF with XAML. They are two different parts of the system. You can just create an app using XAML and then connect up a datastore with EF. It would be best to have a XAML based app, then copy and paste over the relevant code from the EF sample to get what you want.

          1. Pete Suffolk

            I have tried to rework your example using XAML I just get odd errors from db.Database.EnsureCreated();

            Value cannot be null. Parameter name: path1

            And public DbSet Blogs { get; set; } appears to fail too.

            1. Adam Pedley

              If you are getting an error with the Path, it means that the dbPath string isn’t getting passed through to the DataContext is null. Check that you are getting a value from the native platform when its passed through.

              1. Pete Suffolk

                I changed the code for Onconfiguring to:

                protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
                {
                var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = “test.db” };
                var connectionString = connectionStringBuilder.ToString();
                var connection = new SqliteConnection(connectionString);

                optionsBuilder.UseSqlite(connection);
                }
                And I still get the same error.

                Also the DbSet issue may be due to threading.

                1. Adam Pedley

                  You need to specify the path as well as the file name. If you look further up in the post, you will see there is code to retrieve the database path for each platform.

                  1. Pete Suffolk

                    That worked perfectly, thank you

                    I need to ask you lots more questions just to see how narrow these comment boxes get.

  5. Jason

    Hi Adam,

    Thanks for the interesting post. What are your views on EF vs something like Akavache?

    Also, any thoughts on corner-case issues such as your app going to sleep (and eventually being terminated) or a reboot/power-loss in the middle of DB operations corrupting your local DB? I haven’t seen much on db transactions when researching which data approach is best for my apps.

    Thanks again, and Cheers from Florida, USA!

    1. Adam Pedley

      Hi Jason

      EF is an far more powerful ORM, with more features than Akavache. I don’t know of what performance enhancements they make with EF, but I assume over time they will continue to tune it.

      Akavache is made by Paul Betts, but I don’t think there is very much active development on it anymore. Its perfect for temporary local storage, based on key-value pair. Its not good for a full relational local database.

      Both Akavache and EF will support transactions. Hence power loss or reboot shouldn’t be a big deal, if you use transaction for all writes to the DB. Akavache does by default, I think you might have to manually do it in EF.

  6. Rex Hope

    Would it be possible for EF Core and Azure Sync (MobileServiceAzureClient) to work together? If yes, who would create the local db and sync it to azure? Is it EF (code-first) or is it the azure client? I am quite confused of which ever to use, or would it be better for me to just use Azure Client? Thanks!

    1. Adam Pedley

      It wouldn’t be easy to use both, because you need to use the SDK to communicate with the database, which will add and maintain additional sync values. You could possibly do it, using both, but you would need to understand the table structures below and generate your own LastUpdated, Deleted and Version information, when you updated rows.

      Your best bet, might be to roll your own synchronization. I go through some possible ways here: https://xamarinhelp.com/mobile-database-bi-directional-synchronization-rest-api/

      1. Rex Hope

        Thanks, I checked and browsed through the synchronization api, that’s the other option I am thinking of.

        I just have one clarification for this one, but then if I use the Azure SDK, wouldn’t it be pointless for me to use EF as it would add additional complexity? So wouldn’t be the best choice is to use Azure SDK (as it currently supports .net standard now)?

        Right now, from what I can see, using EF would be easier if I have my own sync api, which then the datas resides on premise, else, Azure if I want data to be resided on azure.

        1. Adam Pedley

          You can use your own Sync API and put it on Azure, as well. The Azure Mobile Client just uses Azure Tables I think, though you might be able to put in on SQL as well. I did previously, but I had to create the tables myself back in the day.

          But lets frame this a different way. If you have a simple data store, and want a quick and easy way to do simple sync to a remote data store on Azure, then Azure Mobile Client is the way to go.

          If you have a complex sync process or data store and want solid control over it. Roll your own sync api and use EF or sqlite-pcl-net.

          1. Rex Hope

            Yep, well aware of the API being hosted on Azure, as well as SQL on Azure.

            Also thanks for this clarification for the syncing process.

  7. Chris

    Nice article. Can you compare this approach with the Realm-approach? Which is one easier, faster, has more features?

    1. Adam Pedley

      I’ll add it to my list. I haven’t used Realm before, though SQLite and Realm have comparable performance in most areas. I would think that EF would have greater capabilities in terms of an ORM, but I am not sure what performance hit you might take for using EF.

  8. Raphaël

    Really nice article!
    Could you elaborate about db migrations ?
    What does happen when the Android version code is increased (when the app is updated)? Same question for iOS which I know less.
    Do we have a callback to manage migrations or does EF does it for us?
    Thanks a lot

    1. Adam Pedley

      Migrations are the same on EF Core regardless of platform, hence it’s probably best to look at microsoft docs for that. I’ll add a note to create a new blog post about it in the future.

      I imagine (if in relation to my example code), that if you call db.Database.Migrate();, on app startup, or when you detect an upgrade in app version number, it will perform the migration for you.

      1. Raphaël

        Alright I will have a look at the official documentation. I thought there would be specific behaviour because of mobile concerns and upgrades. Thank you and I’m glad you will write an article about it sometime.

Leave A Comment?