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

Question

My situation is that I need to query from another database and show the result in my application. Both databases are on the same server. I came up with an idea on creating SQL-View in my database which would query the other database for values that I want. But I am not quite sure on how I can create or map SQL-View from the ABP framework?

I am using the full .Net framework with the Angular template.

yes I do. I think with ABP framework Code first is the only available option, isn't it? — Mohammad Shadmehr, Mar 16 '18 at 03:31

score 2 · Accepted Answer · answered Mar 16 '18 at 06:07

2

Creating View is not directly supported in EF. So you can try the below approach.

Create an empty Migration using Add-Migration.
Write you Create View script in Up method of generated migration and run the script using context.Database.ExecuteSqlCommand method.
Declare your class and use Table as you do for your model class.

[Table("YourViewName")] public class YourClassName {

}

Ignore your view class like this modelBuilder.Ignore<yourClassName>(); in OnModelCreating method.
Run Update-Database in Package Manager Console.

answered Mar 16 '18 at 06:07

Vivek Nuna

25,472
25
109
197

thanks @vivek. would you please also explain what the step 4 does. – Mohammad Shadmehr Mar 17 '18 at 04:17
If you don’t do this, it will try to create table ‘YourViewName’. So by doing this step you are enforcing not to create table, instead you will create a view by using SQL. – Vivek Nuna Mar 17 '18 at 10:58

score 1 · Answer 2 · edited Mar 16 '18 at 06:21

Create your table view in the database. Then using EF FluentAPI to config the view mapping. Here is the sample code:

1. Create POCO class to map:

 public class YourView
 {
        public int Id { get; set; }

        public string Value { get; set; }
 }

2. EF FluentAPI mapping configuration:

Create map class:

 public class YourViewMap : IEntityTypeConfiguration<YourView>
 {
        public void Configure(EntityTypeBuilder<YourView> builder)
        {
            builder.ToTable("YourViewName");
        }
 }

Add mapping configuration to your DbContext (such as AbpCoreDbContext). Override OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new YourViewMap ());
    base.OnModelCreating(modelBuilder);
}

3. Get data:

using IRepository<YourView> to query data from the view.

P.S: related question how to use views in code first entity framework

score 0 · Answer 3 · edited Mar 16 '18 at 07:17

0

Create a new DbContext for your legacy database. You can have multiple DbContext in an ABP application. Each DbContext have its own connection string. Creating view is kinda hackish.

edited Mar 16 '18 at 07:17

Vivek Nuna

25,472
25
109
197

answered Mar 16 '18 at 06:48

Alper Ebicoglu

8,884
1
49
55

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

3 Answers3

Linked