Clever Geek Handbook

Tips for Working with EntityFramework Core

Hello.



I recently discovered that not everyone who works with EF knows how to cook it. Moreover, they are not eager to understand. Facing problems at the very early stages - setting up.

Even after a successful configuration, there are problems with data requests. Not because people don't know LINQ, but because not everything can be mapped from objects to relational models. Because when working with a link, people think in tables. Draw SQL queries and try to translate them into LINQ.



This and, perhaps, something else I want to talk about in the article.



Setting up



A person came to the project, saw EF there for the first time, marveled at such a miracle, learned to use it, decided to use it for personal purposes. Created a new project, ... And then what to do?



You start googling, trial, error. In order to simply connect EF to a project, the developer is faced with incomprehensible kinds of problems.



1. And how to configure it to work with the required DBMS?

2. And how to configure the work of migrations?



I'm sure there are more problems, but these are probably the most frequent ones. Let's talk about everything in order.



1. Well, here it is to google. :) Your task is to find an EntityFramework Core provider for your DBMS.



Also in the description of the provider you will find instructions for setting up.



For PostgreSQL, for example, you need to install the Nuget package Npgsql.EntityFrameworkCore.PostgreSQL

Create your own context accepting DbContextOptions and create the whole thing like this 



var opts = new DbContextOptionsBuilder<MyDbContext>()
        .UseNpgsql(constring);

var ctx = new MyDbContext(opts.Options);




If you have an ASP .NET Core application, the context is registered in the container differently. But you can already see this in your working project. Or google.



2. If there are no nannies in your company, then you most likely know how to install the necessary tools for working with migrations. Whether for a package manager or NET Core CLI.



But what you may not be aware of is that the selected startup project (--startup-project) is started when working with migrations. This means that if the starting project for migrations is the same project that launches your application and when you start for some reason you roll migrations through ctx.Database.Migrate (), then when you try to create delete a previously created migration or create another one, then the last created migration will roll over to the base. SURPRISE!



But, perhaps, when trying to create the first migration, you will catch something like this



No database provider has been configured for this DbContext. A provider can be configured by overriding the DbContext.OnConfiguring method or by using AddDbContext on the application service provider. If AddDbContext is used, then also ensure that your DbContext type accepts a DbContextOptions <TContext> object in its constructor and passes it to the base constructor for DbContext.


This is because the tool that works with migrations creates them based on your context, but for this it needs an instance. And to provide it, you need to implement the IDesignTimeDbContextFactory interface in the starter project. This is not difficult, there is only one method that should return an instance of your context.



Setting up models



You have already created your first migration, but for some reason it is empty. Although you have models.



The point is that you have not taught your context to translate your models into your database.



In order for EF to create a migration to create a table for your model in the database, you must at least create a property of the DbSet <MyEntity> type in your context.



for example, by this property

public DbSet <MyEntity> MyEntities {get; set; }



The MyEntities table will be created with fields corresponding to the properties of the MyEntity entity.



If you don't want to have a DbSet, or if you want to influence the default table creation rules for an entity, you need to override

protected override void OnModelCreating(ModelBuilder modelBuilder)

your context's method .



How to do this, you most likely know. Moreover, you are probably familiar with the attributes that allow you to control the mapping rules. But here's the thing. Attributes do not give complete control over the mapping, which means you will have refinements in OnModelCreating. That is, you will have rules for mapping entities both in the form of annotations and in the form of fluent api. Go then look for why the field has the wrong name, which you expected, or the wrong restrictions.



- Well then, everything is simple, - you say - I will configure everything through the OnModelCreating



And then, after setting up the 20th entity from 10 fields, your eyes start to ripple. You are trying to find the field setting for some entity, but everything floats before your eyes from a uniform block 200-500 lines long.



Yes, you can break this block into 20 methods and it will become a little easier. But it is useful to know that there is an IEntityTypeConfiguration <TEntity> interface, by implementing which for a specific entity, you can describe in this implementation the mapping rules for a specific entity. Well, in order for the context to pick it up, in OnModelCreating you need to write

modelBuilder.ApplyConfigurationsFromAssembly (assemblyWithConfigurations);



And, of course, it is possible to transfer the general behavior to the same OnModelCreating. For example, if you have a general rule for identifiers of all or many entities, you can configure it like this 



foreach (var idEntity in modelBuilder.Model.GetEntityTypes()
    .Where(x => typeof(BaseIdEntity).IsAssignableFrom(x.ClrType))
    .Select(x => modelBuilder.Entity(x.ClrType)))
{
    idEntity.HasKey(nameof(BaseIdEntity.Id));
}


Building queries



Well, we put things in order, it became a little more pleasant.



Now it remains to redo the queries of our home project from SQL to Linq. I've already written requests at work, it's as easy as

shelling pears ctx.MyEntities.Where (condition) .Select (map) .GroupBy (expression) .OrderBy (expression)… ease.



So, what's our request? 



SELECT bla bla bla FROM table
RIGHT JOIN.......


yeah 



ctx.LeftEntities.RightJoin(.... f@#$


I've been using Linq and its extension methods long before I got to know EF. And I never once had a question, but where is RIGHT JOIN, LEFT JOIN in it ...



What is LEFT JOIN in terms of objects?



it 




class LeftEntity 
{
    public List<RightEntity> RightEntities { get; set; }
}


It's not even magic. We just have a certain entity that refers to a list of other entities, but may not have any.



That is, this 



ctx.LeftEntities.Include(x => x.RightEntities)


What is RIGHT JOIN? This is an inverted LEFT JOIN, meaning we just start with a different entity.



But anything happens, sometimes you need control over each bundle as a separate entity, even if the left entity is not associated with any right one (the right one is NULL). Therefore, explicitly LEFT JOIN can be done like this 



ctx.LeftEntities.SelectMany(x => x.RightEntities.DefaultIfEmpty(), (l, r) => new { Left = l, Right = r })


This gives us control over the binding, just like in the relational model. Why might you need it? Let's say a customer needs to display data in the form of a table, and sorting and / or pagination is needed.



I don't like this idea either, but everyone has their own quirks and endless love in Excel. So we will cover the mat with a cough, cursing into a fist, and continue to work. What's next for us? 



FULL JOIN


So, well, I already understood, we do not think with SQL, we think with objects, like this, and now like this ... damn it. How to portray this?



Without tuples, nowhere.



In general, when we have to work with tuples, we lose understanding about the type of connection (1-1, 1-n, nn) ​​and in general, theoretically, we can cross flies and elephants. This is black magic.

Let's do it!



Let's take many-to-many as a basis.



Thus, we have 3 types of entity



LeftEntity

RightEntity

LeftRight (for organizing the connection)



LeftRight I will create with a composite key. I do not need direct access to this entity, external references to it are not required, so I will not produce unnecessary fields and indices.



As a result of the query, I want to get a set of tuples, which will contain the left entity and the right entity. Moreover, if the virgin entity has nothing to do with the right one, then the right object will be null. The same goes for the right-hand entities.



It turns out that LeftRight does not suit us as an output, its limitations will not allow us to create a bundle without one of the entities. If you practice DDD, you will catch inconsistency.

Even if you don't practice, you shouldn't. Let's create a new type for the

LeftRightFull output , which still contains a reference to the left and right entities.



So, we have



Left

L1

L2



Right

R1

R2



LeftRight

L2 R2



We want at the output

L1 n

L2 R2

n R1



Let's start with the left connection 



var query = ctx.LeftEntities
    .SelectMany(x => x.RightLinks.DefaultIfEmpty(), (l, lr) => new LeftRightFull
    {
        LeftEntity = l,
        RightEntity = lr.RightEntity
    })




Now, we already have

L1 n

L2 R2



What's next? Stick RightEntity via SelectMany? So we get

L1 n R1 n

the L1 n R2 the L2

the L2 R2 R1 n

the L2 R2 R2 the L2



weed out the superfluous (L2 R2 R1 n and L1 n R2 L2) on the condition we will be able, however, it remains unclear how L1 n R1 n turn in

L1 n

n R1



Perhaps the excavations took us to the wrong steppe. Let's just connect these similar queries with left joins for left and right entities via Union.

L1 n

L2 R2

UNION

L2 R2

n R1



Here you may have questions about performance. I will not answer them, because everything will depend on the specific DBMS and the meticulousness of its optimizer.



Alternatively, you can create a View with your desired query. Union doesn't work on EF Core 2, so I had to create a View. However, I recommend not to forget about it. Suddenly, you decide to implement soft deletion of entities through the IsDeleted flag. In the view, you need to support this. If you forget, it is not known when you will receive a bug report.



By the way, how to implement soft deletion without rewriting the entire code? I will talk about this next time. Maybe something else.



Goodbye to everyone.


More articles:


All Articles