1

How to create View (SQL) from Entity Framework in ABP Framework

Not allowed to post comments because of reputation. Just trying to get more information on connecting a database to an Entity Framework, without having to switch to a code-first development style. View selected answer's response (he told the OP to basically do the same thing he was going to do in the DB but with EF, and then added an extra step where EF "...ignores..." the previous instructions...

I want to create tables and design database directly in SQL, and have the csharp library just read/write the table values (kind of like how dapper function where it isnt replacing your database, just working along side of it).

The tutorials don't talk about how to integrate your databases with your project. It either brushes over the subject, ignores it completely, or discusses how to replace it.

I don't want to do any EF migrations (i dont want/need to destroy/create database everytime i decide to run, duplicate, or transfer project). Any and all database back-track (back-up/restore) should be done with and thru SQL (within my work environment).

Just to be clear on exactly what i'm trying to learn: How does somebody who specializes in database administration (building database schema, managing and monitoring data, and has existing database with data established) connect to project to fetch data (again, specifically referencing Dapper's Query functionality).

I want to integrate and design micro-services, some may share the same database connection or rely on another. But i just simply want to read data in a clean strongly-typed class entity, and maybe deal with insert/update somewhere else if i have to.

I would prefer to use Dapper instead of EF, but ABP is so heavily integrated with EF's design, it's more of a headache to avoid it, than it is to just go along with.

user3681384
  • 105
  • 9
  • Search for "Code First to an Existing Database", which is the EF workflow that starts with an existing database, and doesn't use Migrations. EG: https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/workflows/existing-database – David Browne - Microsoft Sep 01 '18 at 23:17
  • @DavidBrowne-Microsoft I've researched the uhh... Existing Database issue and have viewed that particular article. But in ABP, there are more hoops to jump thru that are causing me a great deal of confusion, and i'm not sure if the solution is as clear and simple as the article displays it to be. Example [new Entity in ABP](https://aspnetboilerplate.com/Pages/Documents/Entities) says to inherit other classes and some other tutorials reference using `[Table('name')]` attributes... i'm getting a lot of mixed signals, and i wish everything would just follow one simple method. – user3681384 Sep 01 '18 at 23:26
  • Part of my issue as mentioned before, i would love to utilize multiple database connections to create a more complete package to uses smaller components. But ABP has entire design structure that centered around inheriting everything from a single node. Not sure where to branch off from, and if it will cause issues where they are not communicating. Especially since user `Context.Session` is tied to `Abp.DbContext` and the tutorial told me to create a new `DbContext` to sync my database table... idk how to make sense of the different layers of information. – user3681384 Sep 01 '18 at 23:32

1 Answers1

0

You should be able to map EF under ABP the same way as any other project using DB-first configuration.

The consistent approach I use for EF: (DB-First)

  1. Define entities to match the table/view structure.
  2. Define configuration classes extending EntityTypeConfiguration<TEntity> with the associated ToTable(), HasKey(), and any HasMany/HasRequired/HasOptional for relationships as needed.
  3. In DbContext.OnModelCreating: modelBuilder.Configurations.AddFromAssembly(GetType().Assembly); to load all entity configurations. (assuming DbContext is in the same assembly as the models/configurations Substitute GetType().Assembly to point at the entity assembly.
  4. Turn off Migrations. In DbContext constructor: Database.SetInitializer<MyDbContext>(null);

EF offers a lot more than simply mapping tables to classes. By mapping relationships between entities, EF can help generate optimized queries for retrieving data across those related entities. This can allow you to flatten data structures without returning unnecessary data, replace the need for views, and generally reduce the amount of data coming across the wire from the database to the application server.

Steve Py
  • 26,149
  • 3
  • 25
  • 43
  • Just to be clear, steps 2-3 are _not_ needed, right? `What's the point of running an EF migration when you can SQL directly in database?` You don't need to map `HasKey` and `ToTable`, because "...EF can help generate optimized queries for retrieving data across those related entities..." if/when you do? As much as i like the idea of eliminating one task and funneling it thru another. I would still like to keep the role of a DBA intact, which means that i can perform the same task without using C# code to replace SQL? – user3681384 Sep 03 '18 at 13:34
  • What task are you replacing? The configuration classes tell EF about the schema and how it relates. EF can take classes and interpret schema automatically provided your schema follows predictable convention with annotation hints, though with many existing schemas that isn't the case.. You can also use the schema designer to generate an EDMX for EF to use, but that can encounter similar issues. So, as I mentioned, the above was a "consistent approach" that handles existing schemas well. Ultimately the choice is yours. You can always find a different job. :) – Steve Py Sep 03 '18 at 21:26
  • Sorry, i just wanted confirmation that my understanding of how it functions was accurate. There's nothing wrong with firing your DBA and having your C# developer perform SQL/DB requests thru your application. That's just less people and parts getting involved. But i want to know, without changing status quo, is it possible to do the same thing and simply use EF as a mapping tool (exactly the same way that Dapper functions)? I dont want something invasive that causes me to change my way of doing things. I just want it to work with what i already have going on... – user3681384 Sep 03 '18 at 21:36
  • You can create views and map entities to views, though I only recommend that for read-only access. I'm not sure what else would be the issue. EF typically needs some form of configuration to understand the schema. It does not need to be allowed to modify the schema. (migrations) EF does need to know about PKs, though FKs are optional as you can compose linq queries with Joins, but I don't recommend that approach as it's "verbose", and another variation of SQL-like syntax with its own nuances. Mapping relationships lets object models understand the schema. – Steve Py Sep 03 '18 at 22:25