【原】linq to sql 转到linq to entitis几个需要修改地方

  

                   Linq to sql  linq to entits兼容orcle时所遇相关问题

1LINQ to Entities 不支持 LINQ 表达式节点类型“Invoke”

原始代码：var filter = Queryable.Where(wd.WOO_PROJECT, predicate);

更改后代码var filter = wd.WOO_PROJECT.Where(predicate.Compile());

 

2.LINQ to Entities 不识别方法“Int32 ToInt32(System.String)”，因此该方法无法转换为存储表达式。

通常原始代码如下：

Where id=Convert.ToInt32(cousid)

更改后代码：

Var currentid= Convert.ToInt32(cousid);

Wehre id=currentid;

3 LINQ to Entities 不支持 LINQ 表达式节点类型“ArrayIndex”。

int[] idArray = BaseExtensions.StrArrayToIntArray(BaseExtensions.StrToStrArray(ids));

  int count = wd.WOO_COMP_ATTACHMENT.Count(h => h.CATEGORY_ID == idArray[i]);

执行到第二个语句就报上门错误。解决办法

将带索引的对象赋值给一个变量，在进行比较即可

 

int[] idArray = BaseExtensions.StrArrayToIntArray(BaseExtensions.StrToStrArray(ids));

var id= idArray[i];

  int count = wd.WOO_COMP_ATTACHMENT.Count(h => h.CATEGORY_ID ==id);

4.sql方法转化orcle

SqlMethods.DateDiffDay(customer.EXPIRATION_DATETIME.Value, DateTime.Now)),  //逾期

上面计算相差天数。但是只支持sql server

改成：

System.Data.Objects.EntityFunctions.DiffDays(customer.EXPIRATION_DATETIME.Value, DateTime.Now)

5.假如在一个query执行中提示无法识别我们自定义的一个方法。那就去掉那个方法。先toList()

然后把list的结果集作为查询库。然后再调用那个方法

比如下面这种写法

  public string GetContractAttachmentCategories()

        {

            try

            {

                WCMSDATA wd = DataEntity.Initializes();

                var query1 = from f in wd.WOO_CATEGORY

                            where f.TYPE == "CONTRACTFILE"

                            select new

                            {

                                LbID = f.ID,

                                LbName = f.NAME,

                                LBPermission = f.ID

                            };

                query1 = query1.OrderByDescending(a=>a.LbID);

                var lis = query1.ToList();

                var query = from f in lis

                          

                            select new

                            {

                                LbID = f.LbID,

                                LbName = f.LbName,

                           LBPermission =GetCategoryPermissionUser(f.LBPermission)

                            };

            

                string JsonSource = query.ToJSON();  //当前页记录转成JSON格式

                string strJsonSource = @"{";

                strJsonSource = strJsonSource + @"""fileCategoryList"":" + JsonSource + "}";

                return strJsonSource;

            }

            catch (Exception e)

            {

                WooLogger.Error(e.ToString());

                return string.Empty;

            }

        }

上面的GetCategoryPermissionUser这个方法如果放到toList()之前就会报无法识别此方法。

 

 

6  G.Sum(p => p.WOO_CONT_ACTUAL_FINANCE.STATE == "已确认" ? p.AMOUNT_MONEY : 0)执行错误

会提示内部执行错误什么的。那就必须改写

G.Where(p => p.WOO_CONT_ACTUAL_FINANCE.STATE == "已确认").Sum(p=> p.AMOUNT_MONEY)

 

 

7  join报错

var customer = wd.WOO_COMPANY.Where(GetAdvancedCustomerSearch().Compile()); //Queryable.Where(wd.WOO_COMPANY, GetAdvancedCustomerSearch());

                            //var pcfIdsByCustomer = (from pln in wd.WOO_CONT_PLAN_FINANCE

                            //                        join cust in customer on pln.WOO_CONTRACT.COMPANY_ID equals cust.ID

                            //                        where pln.TYPE == "收款"

                            //                        select pln.ID).ToList();  //包含客户条件的计划收款IDs

支持这种写法

改成如下写法

   var pcfIdsByCustomer = (from cust in customer

                         from pln in wd.WOO_CONT_PLAN_FINANCE

                       where pln.TYPE == "收款" && pln.WOO_CONTRACT.COMPANY_ID == cust.ID

                 select pln.ID).ToList();

 

 

问题 ：System.NotSupportedException: LINQ to Entities 仅支持无参数构造函数和初始值

  varsList = wd.WOO_CONT_TEXT_TEMP_VALUE.Where(p => p.IS_CUSTOMER == 0)

                        .OrderByDescending(p => p.ID)

                       .Select(p => new TplContractVariable

                        {

                            VarName = p.ID.ToString(),

                            VarLabel = TemplateAndObjectField.GetFildNameByKey(p.NAME, I18NTypeEnum.enUS)

                        }).ToList<TplContractVariable>();

 

解决

  varsList = wd.WOO_CONT_TEXT_TEMP_VALUE.Where(p => p.IS_CUSTOMER == 0)

                        .OrderByDescending(p => p.ID)

                        .Select(p => new { p.ID, p.NAME })

                        .Select(p => new TplContractVariable

                        {

                            VarName = p.ID.ToString(),

                            VarLabel = TemplateAndObjectField.GetFildNameByKey(p.NAME, I18NTypeEnum.enUS)

                        }).ToList<TplContractVariable>();

 

 

无法创建“WooDataEntity.WOO_PROJECT”类型的常量值。此上下文仅支持基元类型或枚举类型。

var filter = wd.WOO_PROJECT.Where(predicate.Compile());

   var query=   from  ht in wd.WOO_CONTRACT

                          join  xm in filter

 

 

                            on  ht.PROJECT_ID  equals xm.ID

                            where ht.COMPANY_ID == customerID

 

解决方法：调换一下位置

var filter = wd.WOO_PROJECT.Where(predicate.Compile());

   var query=  from xm in filter

               join ht in wd.WOO_CONTRACT

                         

 

                            on xm.ID equals ht.PROJECT_ID

                            where ht.COMPANY_ID == customerID