Query records with a stored procedure in Entity Framework's database-first approach while also handing in variables!

Let's start with a simple example:

public async Task<Division> GetDivision(string businessDivisionCd, DateTime
      finalFriday)
{
   Division division = new Division(businessDivisionCd);
   var date = new SqlParameter("@ContractFuturesMonth", finalFriday);
   var name = new SqlParameter("@BusinessDivisionCd", businessDivisionCd);
   var contracts = await _dbSetDivisionContracts
         .FromSql("UI.GetContractByBusinessDivisionCd @ContractFuturesMonth,
         @BusinessDivisionCd", date, name).ToListAsync();
   division.Contracts = contracts;
   return division;
}

 
 

Great! We don't explicitly state what type contracts is of above but the collection will have to be of a type that is friendly to Entity Framework in that, at the very least, the [Key] attribute must decorate one of the getsetters to denote a primary key (for which you will need the System.ComponentModel.DataAnnotations namespace looped in the using declarations) and that getsetter and all of the other getsetters at the type must match up one-to-one with the column names of the columns being queried out of the stored procedure. If you want "better" names you can turn around and map what emerges to a different type with AutoMapper. Let's look at a more complicated example in which we hand in a user-defined table type for one of the variables:

public async Task<List<Division>> GetDivision(string[] businessDivisionCds, DateTime
      finalFriday)
{
   List<Division> divisions = new List<Division>() { };
   DataTable scope = new DataTable("BusinessDivisionCds");
   scope.Columns.Add(new DataColumn("BusinessDivisionCd", typeof(string)));
   foreach(string businessDivisionCd in businessDivisionCds)
   {
      var row = scope.NewRow();
      row["BusinessDivisionCd"] = businessDivisionCd;
      scope.Rows.Add(row);
   }
   var date = new SqlParameter("@ContractFuturesMonth", finalFriday);
   var name = new SqlParameter()
   {
      SqlDbType = SqlDbType.Structured,
      ParameterName = "@BusinessDivisionCds",
      TypeName = "dbo.BusinessDivisionCds",
      Value = scope
   };
   var contracts = await _dbSetDivisionContracts
         .FromSql("UI.GetContractByBusinessDivisionCd @ContractFuturesMonth,
         @BusinessDivisionCds", date, name).ToListAsync();
   foreach (string businessDivisionCd in businessDivisionCds)
   {
      divisions.Add(new Division(businessDivisionCd)
      {
         Contracts = contracts.Where(c => c.BusinessDivisionCd ==
               businessDivisionCd).ToList()
      });
   }
   return divisions;
}

 
 

All of the above is C#, but everything that follows is T-SQL. dbo.BusinessDivisionCds could look like:

USE [EnterpriseConsumption]
GO
CREATE TYPE [dbo].[BusinessDivisionCds] AS TABLE(
   [BusinessDivisionCd] [varchar](50) NULL
)
GO

 
 

The stored procedure:

USE [EnterpriseConsumption]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [UI].[GetContractByBusinessDivisionCd]
   @FinalFriday DATETIME,
   @BusinessDivisionCds dbo.BusinessDivisionCds READONLY
AS
BEGIN
   set nocount on;
   DECLARE @AdminOrReporterAttestationCount int
   DECLARE @AdminOrReporterAttestation TABLE
   (
      ReportDate DATETIME NOT NULL,
      Id int NULL,
      AttestedBy varchar(50) NULL,
      AttestedDate DATETIME NULL
   )
   INSERT INTO @AdminOrReporterAttestation
   SELECT TOP 1 ReportDate, CFTCReportID, AttestedBy, AttestedDate FROM
         dbo.CFTCReport
   WHERE ReportDate = @FinalFriday
   SELECT @AdminOrReporterAttestationCount=COUNT(ReportDate) FROM
         @AdminOrReporterAttestation
   select
      c.ContractId
         ,c.CFTCReportID
         ,c.CHSLocationID
         ,c.BusinessPartnerTypeID
         ,c.ContractStatusID
         ,c.CommodityID
         ,c.ContractDate
         ,c.ContractDeliveryDate
         ,c.ContractDueDate
         ,c.ContractFuturesMonth
         ,c.ContractODSID
         ,c.ContractNbr
         ,c.ContractTypeCd
         ,c.ContractTypeDesc
         ,c.ContractCategoryCd
         ,c.ContractCategoryDesc
         ,c.PricingDate
         ,c.Price
         ,c.Currency
         ,c.PriceStatusDesc
         ,c.IsPriced
         ,c.isInTransit
         ,c.isReceived
         ,c.SourceTotalQty
         ,c.SourceAppliedQty
         ,c.SourceRemainingQty
         ,c.UOMID
         ,c.CFTCKBUQty
         ,c.CFTCStandardQty
         ,c.isBUAttested
         ,c.BUAttestedBy
         ,c.BUAttestedDate
         ,c.isDivAttested
         ,c.DivAttestedBy
         ,c.DivAttestedDate
         ,a.AttestedBy as 'AdminOrReporterAttestedBy'
         ,a.AttestedDate as 'AdminOrReporterAttestedDate'
         ,c.is204
         ,c.SourceID
         ,c.SourceSystemID
         ,c.SourceKey
         ,c.BusinessUnitID
         ,bu.BusinessDivisionCd
         ,bu.BusinessDivisionDesc
         ,bu.BusinessUnitDesc
         ,bu.isDisplayUI
         ,c.CreDate
         ,c.UpdDate
         ,c.CreBy
         ,c.UpdBy
   FROM [dbo].[Contract] c
      INNER JOIN [dbo].[BusinessUnit] bu
   ON c.BusinessUnitID = bu.BusinessUnitID
      LEFT JOIN @AdminOrReporterAttestation a
      ON c.CFTCReportID = a.Id
   WHERE c.ContractFuturesMonth = @FinalFriday AND bu.BusinessDivisionCd IN
         (select BusinessDivisionCd from @BusinessDivisionCds)
END