C# UpsilabEntities.ExecuteStoreQuery方法代码示例

 public static List<DateSouscriptionModel> GetDateSouscriptionByClient(Guid idclient)
 {
     using (UpsilabEntities db = new UpsilabEntities())
     {
         return db.ExecuteStoreQuery<DateSouscriptionModel>("exec agreg.GetDateDeSouscriptionCompteByClient @IdClient", new SqlParameter("@IdClient", idclient)).ToList<DateSouscriptionModel>();
     }
 }

        public static List<AgregSuiviCommercialModel> GetListClientsSuiviCommercial(Guid pSelectedUserId, bool? isCustomer, Upsilab.Data.Model.User pCurrentUser, FirmInstitution pFirmInstitutionParent, List<FirmInstitution> pLstFirmInstitutions)
        {
            using (UpsilabEntities db = new UpsilabEntities())
            {
                string sIdFirms = string.Empty;

                List<string> idfirms = pLstFirmInstitutions.Select(f => f.idFirmInstitution.ToString()).ToList();
                if (idfirms != null && idfirms.Count() > 0)
                {
                    sIdFirms = string.Join(",", idfirms.ToArray());
                }

                List<AgregSuiviCommercialModel> resultat = db.ExecuteStoreQuery<AgregSuiviCommercialModel>("exec GetListClientsSuiviCommercial @idUser, @isSingleUserDB ,@isCustomer ,@lstIdFirm", new SqlParameter("@idUser", pSelectedUserId), new SqlParameter("@isSingleUserDB", pFirmInstitutionParent.SingleUsersDB), new SqlParameter("@isCustomer", isCustomer), new SqlParameter("@lstIdFirm", sIdFirms)).ToList<AgregSuiviCommercialModel>(); ;

                return resultat;
            }
        }

 public static string getNiveaudeRisque(int idcompte,Guid idClient)
 {
     using (UpsilabEntities context = new UpsilabEntities())
     {
         try
         {
             var result = context.ExecuteStoreQuery<string>("select agreg.frontalweb_quantalyscalcrisque(@idcompte,@idClient)", new SqlParameter("idcompte", idcompte), new SqlParameter("idClient", idClient));
             return result.FirstOrDefault();
         }
         catch(Exception ex)
         {
             Upsilab.Business.Log.Log.AppendException(ex);
             return string.Empty;
         }
     }
 }

        public static List<AgregChart> GetOPCVMByCompteClient(int idcompte)
        {            
            List<AgregChart> listeChart = new List<AgregChart>(); 
            using (UpsilabEntities db = new UpsilabEntities())
            {                
                /*listeChart = (from support in db.ag_upsideo_support
                              join ctsupport in db.ag_upsideo_cptrelsupports.Include("ag_upsideo_support").Include("ag_upsideo_compte") on support.idfrontsupport equals ctsupport.idfrontsupport
                              join cgp in db.ag_upsideo_cgp on support.idcrmcabinet equals cgp.idcrmcabinet                              
                              join cpt in db.ag_upsideo_compte on ctsupport.Idfrontcompte equals cpt.Idfrontcompte
                              where support != null 
                              && cgp.idcgp == idCgp 
                              && support.deleted == "n" && cpt.deleted == "n"
                              && cpt.Idfrontcompte == idcompte
                              select ctsupport).ToList()
                                .GroupBy(s => s.ag_upsideo_support.nomsupport)
                                .Select(s => new
                                {
                                    nomsupport = s.Key,
                                    //total = s.Count()
                                    total=s.Sum(x=>x.ag_upsideo_compte.totalcompte.Value)
                                }).ToDictionary(d => d.nomsupport, d => d.total);*/

                listeChart = db.ExecuteStoreQuery<AgregChart>("exec GetChartOPCVM @idcompte,''", new SqlParameter("@idcompte", idcompte)).ToList<AgregChart>();
            }

            return listeChart;
        }

        /// <summary>
        /// Repartition par class d'actif par compte d'un client
        /// </summary>
        /// <param name="idCgp"></param>
        /// <param name="idclient"></param>
        /// <param name="idcompte"></param>
        /// <returns></returns>
        public static List<AgregChart> GetActifByCompteClient(Guid idCgp, Guid idclient, int idcompte)
        {
            List<AgregChart> listeChart = new List<AgregChart>();
            using (UpsilabEntities db = new UpsilabEntities())
            {
                
                listeChart = db.ExecuteStoreQuery<AgregChart>("exec GetChartActif @idcompte,''", new SqlParameter("@idcompte", idcompte)).ToList<AgregChart>();
            }

            return listeChart;
        }

        public static List<AgregChart> GetEvalValoByCompte(int idCompte)
        {
            List<AgregChart> listrepartition = new List<AgregChart>();

            using (UpsilabEntities db = new UpsilabEntities())
            {
                listrepartition = db.ExecuteStoreQuery<AgregChart>("exec GetChartEncourCompte @Idfrontcompte", new SqlParameter("@Idfrontcompte", idCompte)).ToList<AgregChart>();
            }

            return listrepartition;
        }  

        public ActionResult GetPerfs(int id, int type)
        {
            try
            {
                double perfValue = 0;                
                int calcultype = 2;//todo                
               
                using (UpsilabEntities db = new UpsilabEntities())
                {
                    switch (calcultype)
                    {
                        case 0:                            
                        case 1:
                        case 2:                          
                        case 3:
                            
                            List<double> resultat = db.ExecuteStoreQuery<double>("exec GraphePerformance @idcompte,@IsSouscription,0,1", new SqlParameter("@idcompte", id), new SqlParameter("@IsSouscription", type)).ToList<double>();
                            perfValue = resultat.FirstOrDefault();
                            break;
                        default:
                            break;

                    }
                }

                string message = string.Format("Performance = {0}", perfValue);
                if (perfValue > 0)
                {
                    message = string.Format("{1:0.00} % <img src='{0}' width='16' height='16' border='0' class='tooltip tooltipstered' />", @Url.Content("~/Content/images/picto-arr2.png"), perfValue);
                }
                else if (perfValue == 0)
                {
                    //message = string.Format("{1:0.00} % <img src='{0}' width='16' height='16' border='0' class='tooltip tooltipstered' />", @Url.Content("~/Content/images/picto-arr1.png"), perfValue);
                    message = "--";
                }
                else
                {
                    message = string.Format("{1:0.00} % <img src='{0}' width='16' height='16' border='0' class='tooltip tooltipstered' />", @Url.Content("~/Content/images/picto-arr3.png"), perfValue);
                }
                return Content(message);                
            }
            catch (Exception ex)
            {
                //// TODO Erreur de division par zero dans la fonction XIRR
                //if (ex is SqlException)
                //{
                //    //string message = string.Format("{1:0.00} % <img src='{0}' width='16' height='16' border='0' class='tooltip tooltipstered' />", @Url.Content("~/Content/images/picto-arr1.png"), 0);
                //    string message = "--";
                //    return Content(message);  
                //}

                Upsilab.Business.Log.Log.AppendException(ex);
                string message = "--";
                return Content(message);  
            }
        }

        public static object GetProfilCalculer(Guid idCustomer)
        {
            using (UpsilabEntities db = new UpsilabEntities())
            {
                db.CommandTimeout = 180;
                var resultat = db.ExecuteStoreQuery<string>("exec GetProfilCalculer @idCustomer", new SqlParameter("@idCustomer", idCustomer)).FirstOrDefault<string>();

                return resultat;
            }
        }

        public static List<OPCVMModel> GetOpcvm(Guid idCgp)
        {            
            List<OPCVMModel> ListeOPCVM = new List<OPCVMModel>();         

            using (UpsilabEntities db = new UpsilabEntities())
            {
                
                ListeOPCVM = db.ExecuteStoreQuery<OPCVMModel>("exec GetOpcvm @idcgp", new SqlParameter("@idcgp", idCgp)).ToList<OPCVMModel>();
               
            }

            return ListeOPCVM;
        }

        public static List<AgregSupport> GetTop10OPCVM(Guid idCgp)
        {
            List<AgregSupport> ListeOPCVM = new List<AgregSupport>();

            using (UpsilabEntities db = new UpsilabEntities())
            {                             
                
                ListeOPCVM = db.ExecuteStoreQuery<AgregSupport>("exec GetTop10opcvm @idcgp", new SqlParameter("@idcgp", idCgp)).ToList<AgregSupport>();
            }

            return ListeOPCVM;            
        }

        public static List<AgregChart> GetCompteHistoByIdCgp(Guid idCgp)
        {
            List<AgregChart> listrepartition = new List<AgregChart>();
            
            using (UpsilabEntities db = new UpsilabEntities())
            {                
                listrepartition = db.ExecuteStoreQuery<AgregChart>("exec GetChartEncourCgp @idcgp", new SqlParameter("@idcgp", idCgp)).ToList<AgregChart>();
            }

            return listrepartition;
        }       

 public static List<SqlAgregCompteDetail> GetCompteDetail(int idCompte)
 {
     List<SqlAgregCompteDetail> resultat = new List<SqlAgregCompteDetail>();
     using (UpsilabEntities context = new UpsilabEntities())
     {
         resultat = context.ExecuteStoreQuery<SqlAgregCompteDetail>("exec GetCompteDetail @idcompte", new SqlParameter("@idcompte", idCompte)).ToList<SqlAgregCompteDetail>();
     }
     return resultat;
 }

 public static List<SqlAgregCompteDetail> GetCompteDetailContratAgrege(Guid idClient)
 {
     List<SqlAgregCompteDetail> resultat = new List<SqlAgregCompteDetail>();
     using (UpsilabEntities context = new UpsilabEntities())
     {
         resultat = context.ExecuteStoreQuery<SqlAgregCompteDetail>("exec GetCompteContratAgrege @idClient", new SqlParameter("@idClient", idClient)).ToList<SqlAgregCompteDetail>();
         resultat = resultat.Where(x => x.Qteferme != 0).GroupBy(x => x.Nomsupport).Select(x => x.First()).ToList();
     }
     return resultat;
 }

 public static List<SqlAgregCompteModel> GetComptesClient(Guid idClient)
 {            
     List<SqlAgregCompteModel> resultat = new List<SqlAgregCompteModel>();
     using (UpsilabEntities context = new UpsilabEntities())
     {
         context.CommandTimeout = 180;
         resultat = context.ExecuteStoreQuery<SqlAgregCompteModel>("exec GetComptesClient @idClient", new SqlParameter("@idClient", idClient)).ToList<SqlAgregCompteModel>();
     }
     return resultat;
 }

        public static List<AgregClientModel> GetListAgregClientByCodeisin(Guid idCgp, string codeisin, bool? isCustomer, Upsilab.Data.Model.User pCurrentUser, FirmInstitution pFirmInstitutionParent, List<FirmInstitution> pLstFirmInstitutions)
        {
            using (UpsilabEntities db = new UpsilabEntities())
            {
                string sIdFirms = string.Empty;

                List<string> idfirms = pLstFirmInstitutions.Select(f => f.idFirmInstitution.ToString()).ToList();
                if (idfirms != null && idfirms.Count() > 0)
                {
                    sIdFirms = string.Join(",", idfirms.ToArray());
                    //sIdFirms = string.Format("'{0}'",sIdFirms);
                }

                db.CommandTimeout = 180;
                List<AgregClientModel> resultat = db.ExecuteStoreQuery<AgregClientModel>("exec GetListAgregClientByCodeisin @idUser,@codeisin, @isSingleUserDB ,@isCustomer ,@lstIdFirm", new SqlParameter("@idUser", idCgp), new SqlParameter("@codeisin", codeisin), new SqlParameter("@isSingleUserDB", pFirmInstitutionParent.SingleUsersDB), new SqlParameter("@isCustomer", isCustomer), new SqlParameter("@lstIdFirm", sIdFirms)).ToList<AgregClientModel>();

                return resultat;
            }
        }