Tuesday, June 12, 2012

EF complex or bulk inserts from a select statement

Compose complex inserts from a select statement.
int Insert<T>(IQueryable query,IQueryable<T> targetSet)
{
      var oQuery=(ObjectQuery)this.QueryProvider.CreateQuery(query.Expression);
        var sql=oQuery.ToTraceString();
        var propertyPositions = GetPropertyPositions(oQuery);

        var targetSql=((ObjectQuery)targetSet).ToTraceString();
        var queryParams=oQuery.Parameters.ToArray();
        System.Diagnostics.Debug.Assert(targetSql.StartsWith("SELECT"));
        var queryProperties=query.ElementType.GetProperties();
        var selectParams=sql.Substring(0,sql.IndexOf("FROM "));
        var selectAliases=Regex.Matches(selectParams,@"\sAS \[([a-zA-Z0-9_]+)\]").Cast<Match>().Select(m=>m.Groups[1].Value).ToArray();
        
        var from=targetSql.Substring(targetSql.LastIndexOf("FROM [")+("FROM [".Length-1));
        var fromAlias=from.Substring(from.LastIndexOf("AS ")+"AS ".Length);
        var target=targetSql.Substring(0,targetSql.LastIndexOf("FROM ["));
        target=target.Replace("SELECT","INSERT INTO "+from+" (")+")";
        target=target.Replace(fromAlias+".",string.Empty);
        target=Regex.Replace(target,@"\sAS \[[a-zA-z0-9]+\]",string.Empty);
        var insertParams=target.Substring(target.IndexOf('('));
        target = target.Substring(0, target.IndexOf('('));
        var names=Regex.Matches(insertParams,@"\[([a-zA-Z0-9]+)\]");
    
        var remaining=names.Cast<Match>().Select(m=>m.Groups[1].Value).Where(m=>queryProperties.Select(qp=>qp.Name).Contains(m)).ToArray(); //scrape out items that the anonymous select doesn't include a name/value for
         
          //selectAliases[propertyPositions[10]]
          //remaining[10]
        var insertParamsOrdered = remaining.Select((s, i) => new { Position = propertyPositions[i], s })
        .OrderBy(o => o.Position).Select(x => x.s).ToArray();
      var insertParamsDelimited = insertParamsOrdered.Aggregate((s1, s2) => s1 + "," + s2);
      var commandText = target + "(" + insertParamsDelimited + ")" + sql;
        var result=this.ExecuteStoreCommand(commandText,queryParams.Select(qp=>new System.Data.SqlClient.SqlParameter{ ParameterName=qp.Name, Value=qp.Value}).ToArray());
      return result;
}
With some help from http://stackoverflow.com/questions/7808607/how-does-entity-framework-manage-mapping-query-result-to-anonymous-type and http://msdn.microsoft.com/en-us/library/ee358769.aspx



var daInfo = from scp in priceListModel.QueryServiceCodePrice().Where(x => x.RegionID == regionID)
        join sc in serviceCodes
        on scp.ServiceCodeID equals sc
        join scu in scenarioCurrencySource.Query().Where(x => x.DealID == dealID && x.ScenarioNumber == scenarioNumber)
        on new { scp.PriceListID, scp.CurrencyCode } equals new { scu.PriceListID, scu.CurrencyCode }
        join scol in scenarioCountrySource.Query()
        on new { scu.DealID, scu.ScenarioNumber, scp.PriceListID } equals
        new { scol.DealID, scol.ScenarioNumber, scol.PriceListID } into scoLeft
        from sco in scoLeft.DefaultIfEmpty()
        join ccl in referenceModel.QueryCountryCurrencies()
        on new { scu.CurrencyCode, sco.CountryCode } equals new { ccl.CurrencyCode, ccl.CountryCode } into ccLeft
        from cc in ccLeft.DefaultIfEmpty()
        join sbl in scenarioBranchSource.Query()
        on new { scu.DealID, scu.ScenarioNumber, scu.PriceListID, scp.BranchID } equals 
        new { sbl.DealID, sbl.ScenarioNumber, sbl.PriceListID, BranchID=(int?) sbl.BranchID } into sbLeft
        from sb in sbLeft.DefaultIfEmpty()
        join bcl in referenceModel.QueryCountryBranches()
        on new { sb.BranchID, sco.CountryCode } equals new { bcl.BranchID, bcl.CountryCode } into bcLeft
        from bc in bcLeft.DefaultIfEmpty()
        join sccl in serviceCodeModel.QueryServiceCodeCost()
        on new { RegionID = regionID, scp.ServiceCodeID, scp.BusinessSegmentID } equals new { sccl.RegionID, sccl.ServiceCodeID, sccl.BusinessSegmentID } into sccLeft
        from scc in sccLeft.DefaultIfEmpty()
        join scccl in serviceCodeModel.QueryServiceCodeCostCountry()
        on new { scp.RegionID, scp.ServiceCodeID, sco.CountryCode, scp.BusinessSegmentID } equals new
        {
         scccl.RegionID,
         scccl.ServiceCodeID,
         scccl.CountryCode,
         scccl.BusinessSegmentID
        } into scccLeft
        from sccc in scccLeft.DefaultIfEmpty()
        join cur in referenceModel.QueryCurrency()
        on scu.CurrencyCode equals cur.Code
        join d in dealModel.Query()
        on new { scu.DealID, scp.BusinessSegmentID } equals new { d.DealID, d.BusinessSegmentID }
        where cc.CountryCode != null || sco.CountryCode == null
        select new
        {
         scu.DealID,
         scu.ScenarioNumber,
         scp.ServiceCodeID,
         sco.CountryCode,
         scu.CurrencyCode,
         scu.PriceListID,
         bc.BranchID,
         cur.ExchangeRate,
         
         ActualPrice = scp.StandardPrice * cur.ExchangeRate,
         CurrentPrice = scp.StandardPrice * 1,
         StandardPrice = scp.StandardPrice,
         Threshold1 = sccc != null ? sccc.Threshold1 : scc.Threshold1,
         Threshold2 = sccc != null ? sccc.Threshold2 : scc.Threshold2,
         Threshold3 = sccc != null ? sccc.Threshold3 : scc.Threshold3,
         scp.MinVol,
         scp.MaxVol,
         
         scp.MinPaymentAmount,
         scp.MaxPaymentAmount
        };
    
    dealActivitySource.Insert(daInfo);

No comments:

Post a Comment

Post a Comment