Cannot convert Linq to SQL

Question

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.

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.

My database:

My Code:

 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();

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?

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

Answer 1

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 ....

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();