无法将Linq转换为SQL

本文介绍了无法将Linq转换为SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

因此，对于分配，我必须将通过API获得的数据转换为对象并将该对象保存在数据库中.由于该对象没有唯一的任何属性，因此数据库中有多个主键列.

So for a assignement I got I have to convert the data I get through an API into a object and save that object in the database. Since the object doesn't have a any property that is unique there are multiple primary key columns in my database.

因此，我基本上要做的是从数据库中合并"到数据库，因此如果数据已更改为更新(如果数据是新数据)，请执行插入操作.出于某种原因，我的linq查询到此错误.

So what I basically have to do is a 'merge' from to my database so if data has been changed to a update if data is new do an insert. For some reason one my linq queries for this is giving an error.

我的数据库:

我的代码:

public void Insert(List<MEDSU1> medsu1s) { var keys = medsu1s.Select(y => new { SUPP = y.S1SUPP?.Trim() ?? null, COMP = y.S1COMP?.Trim() ??null }).ToList(); List<MEDSU1> medusFromSql = MediusDataContext.MEDSU1s.Where(y => keys.Any(z => z.SUPP == y.S1SUPP && z.COMP == y.S1COMP)).ToList(); var toUpdate = from medsu1org in MediusDataContext.MEDSU1s join sqlkeys in medusFromSql on new { aa = medsu1org.S1COMP, bb = medsu1org.S1SUPP } equals new { aa = sqlkeys.S1COMP, bb = sqlkeys.S1SUPP } select new { sql = medsu1org, obj = sqlkeys, }; toUpdate.ToList().ForEach(y => { y.obj.S1COMP= y.sql.S1COMP; y.obj.S1SUPP = y.sql.S1SUPP; y.obj.S1SUNA = y.sql.S1SUNA; y.obj.S1BAAC = y.sql.S1BAAC; y.obj.S1VATN = y.sql.S1VATN; y.obj.S1COUN = y.sql.S1COUN; y.obj.S1PREF = y.sql.S1PREF; y.obj.S1TAXD = y.sql.S1TAXD; y.obj.S1CURR = y.sql.S1CURR; y.obj.S1TYPE = y.sql.S1TYPE; y.obj.S1ACNR = y.sql.S1ACNR; y.obj.S1ACNM = y.sql.S1ACNM; y.obj.S1EINV = y.sql.S1EINV; y.obj.S1DLAY = y.sql.S1DLAY; y.obj.S1TERM = y.sql.S1TERM; y.obj.S1PYEE = y.sql.S1PYEE; }); var toInsert = medsu1s.Except(toUpdate.Select(y => y.obj)).ToList(); MediusDataContext.MEDSU1s.InsertAllOnSubmit(toInsert); MediusDataContext.SubmitChanges(); }

代码中给我错误的部分是:

The part of the code that is giving me the error is the:

var keys = medsu1s.Select(y => new { SUPP = y.S1SUPP?.Trim() ?? null, COMP = y.S1COMP?.Trim() ??null }).ToList(); List<MEDSU1> medusFromSql = MediusDataContext.MEDSU1s.Where(y => keys.Any(z => z.SUPP == y.S1SUPP && z.COMP == y.S1COMP)).ToList();

我认为这与使用.Any不正确并将其转换为列表有关，但我不知道要修复它.有人可以向我解释我在做什么错吗?

I think it has to do with me using .Any incorrectly and converting it to a List but i dont know to fix it. Can someone explain me what I'm doing wrong?

即时消息错误:在LINQ to SQL实现中，除包含运算符外，不能在查询中使用本地序列."

ERROR im getting : "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator."

推荐答案

由于要处理复合键，因此必须创建一个基本上像

Since you're dealing with a compound key you'll have to create a query that basically looks like

Select * From MEDSU1s Where (S1SUPP == @S1SUPP1 && S1COMP == @S1COMP1) OR (S1SUPP == @S1SUPP2 && S1COMP == @S1COMP2) OR ....

要使用Linq做到这一点，您必须按程序构建表达式.请注意，我假设这些列都是字符串，所以请根据需要更改它们的类型.另外，我也没有很好的方法来测试这一点，但希望这可以帮助您开始寻找解决方案.

To do that with Linq you'd have to build the expression procedurally. Note that I'm assuming the columns are both strings, so change the type of them as needed. Also I have no good way of testing this out but hopefully this can get you started towards a solution.

var queryableData = MediusDataContext.MEDSU1s; var table = Expression.Parameter(typeof(MEDSU1s), "x"); var suppCol = Expression.Property(table, typeof(string), "S1SUPP"); var compCol = Expression.Property(table, typeof(string), "S1COMP"); Expression condition = Expression.Equal(Expression.Constant(1), Expression.Constant(0)); foreach (var x in keys) { var key1 = Expression.Equal(suppCol, Expression.Constant(x.SUPP)); var key2 = Expression.Equal(compCol, Expression.Constant(x.COMP)); var both = Expression.AndAlso(key1, key2); condition = Expression.OrElse(condition, both); } var whereExpression = Expression.Call( typeof(Queryable), "Where", new Type[] { queryableData.ElementType }, queryableData.Expression, Expression.Lambda<Func<MEDSU1s, bool>>( condition, new ParameterExpression[] { table })); var medusFromSql = queryableData.Provider.CreateQuery<MEDSU1s>(whereExpression).ToList();