Calling stored procedures in Entity Framework #465

5 Answer(s)

• 

  0

  ole created 9 years ago

  I did use SQL directly in one of my repository classes.

  public List<ValueDto> GetAll(int id) {

          var ret = Context.Database.SqlQuery<ValueDto>("<SQL returning a result set>").ToList();
          return ret;
      }
  

  The SQL may be a SELECT or an excec of a stored procedure. Entity Framework will try to map the result set to the ValueDto class. Context is defined in the base Abp.EntityFramework.Repositories

  -ole

  Save Cancel
• 

  0

  hikalkan created 9 years ago

  Support Team

  This solution looks fine. Thanks for sharing with us.

  Save Cancel
• 

  0

  doubledp created 9 years ago

  Apologies for only replying now. The repositories is defined within the Entity Framework layer and the DTO's within the Application layer right?

  Currently I have a 2nd dbContext class as per below:

  using System.Data.Common;
  using System.Data.Entity;
  using Abp.EntityFramework;
  using WebApplication.Storage;
  
  namespace WebApplication.EntityFramework
  {
      public class SecondDbContext : AbpDbContext
      {
          public virtual IDbSet<BinaryObject> BinaryObjects { get; set; }
  
          public SecondDbContext() 
              : base("Second")
          {
  
          }
  
          public SecondDbContext(string nameOrConnectionString)
              : base(nameOrConnectionString)
          {
  
          }
  
          public SecondDbContext(DbConnection dbConnection)
              : base(dbConnection, true)
          {
  
          }
      }
  }
  

  Then I have added another it's equivalent base repository class as per below:

  using Abp.Domain.Entities;
  using Abp.EntityFramework;
  using Abp.EntityFramework.Repositories;
  
  namespace WebApplication.EntityFramework.Repositories
  {
      public abstract class SecondRepositoryBase<TEntity, TPrimaryKey> : EfRepositoryBase<SecondDbContext, TEntity, TPrimaryKey>
          where TEntity : class, IEntity<TPrimaryKey>
      {
          protected SecondRepositoryBase(IDbContextProvider<SecondDbContext> dbContextProvider)
              : base(dbContextProvider)
          {
  
          }
      }
  
      public abstract class SecondRepositoryBase<TEntity> : SecondRepositoryBase<TEntity, int>
          where TEntity : class, IEntity<int>
      {
          protected SecondRepositoryBase(IDbContextProvider<SecondDbContext> dbContextProvider)
              : base(dbContextProvider)
          {
  
          }
      }
  }
  

  Do I create a custom repositories for the stored procedures as per your suggestion for each module that I have?

  What I don't understand is in a AppService the IRepository<Entity> is in the constructor that injects the repository accordingly. I assume that this resolved correctly because of the IDbSet<Entity> within the 1st DbContext class.

  How does this then work if I won't have a entity in the newly created 2nd DbContext class?

  Excuse my ignorance, this is fairly new to me and I would really like to understand how everything fits together.

  Save Cancel
• 

  0

  hikalkan created 9 years ago

  Support Team

  Hi,

  Do I create a custom repositories for the stored procedures as per your suggestion for each module that I have?

  It seems like that. If codes are identical, you can create a common repository or a helper class and write SP code in this class and use it from two modules.

  How does this then work if I won't have a entity in the newly created 2nd DbContext class?

  When you create a new entity and add it to the second dbcontext, you can inject IRepository<NewEntity> as normally. ABP understand which dbcontext defines the entity and create correct repository class.

  If same entity is defined in more than one dbcontext, then it resolves the first one. If you need to get a repository for second one, check the module I prepared for the sample:

  <a class="postlink" href="https://github.com/aspnetboilerplate/sample-blog-module/blob/master/src/Abp.Samples.Blog.EntityFramework/AbpZeroSampleDbContext.cs">https://github.com/aspnetboilerplate/sa ... Context.cs</a>

  AutoRepositoryTypes defined repository types for thi dbcontext. And this is the usage:

  <a class="postlink" href="https://github.com/aspnetboilerplate/sample-blog-module/blob/master/src/Abp.Samples.Blog.Application/Posts/PostAppService.cs#L16">https://github.com/aspnetboilerplate/sa ... ice.cs#L16</a>

  So, when you have 2 dbcontextes, you should distinguish them in some level.

  Save Cancel
• 

  0

  doubledp created 9 years ago

  When you create a new entity and add it to the second dbcontext, you can inject IRepository<NewEntity> as normally. ABP understand which dbcontext defines the entity and create correct repository class.

  Does that then mean that I only create the entity in the domain layer and omit the IDbSet<Entity> in the dbContext?

  My understanding is that setting IDbSet<Entity> or DbSet<Entity> is what actually tells EF to create the table on the database when doing a migration.

  I obviously don't want to create tables as I call the SP that aggregates the data and returns it in a result set. Subsequent to that I will also have a SP for the detail of the aggregation as a drilldown.

  Sorry for all the questions; my SQL is stronger than my C# and I am also still getting to grips with O-O programming, as I have come from a procedural language background. :cry:

  Save Cancel