LINQ Compare 2 Tables (Sort of like SQL Except?)-CodePudding

I currently have 2 tables:

Table1, with the following columns:

Id, TypeId, VersionId, AnotherColumn1, AnotherColumn2, AnotherColumn3

Table2, with the following columns:

Id, TypeId, VersionId, DifferentColumn1, DifferentColumn2

The only thing these 2 tables have in common are TypeId and VersionId.

I am trying to get only the TypeId and VersionId from Table1 AS LONG as that specific **Type Id VersionId combination **is not in Table 2.

I have tried the following:

var result1 = this.Table2
    .Select(k => new { TypeId = k.TypeId, VersionId = k.VersionId })
    .ToArray() // Trying to first select all possible TypeId   VersionId combinations from Table2

var finalResult = this.Table1
    . // This is where I am lost, should I use a `.Except`?, some kind of `.Where`?

CodePudding user response：

This should be possible with grouping a left outer join using DefaultIfEmpty:

var results = context.Table1
    .GroupJoin(context.Table2, 
         t1 => new {t1.TypeId, t1.VersionId}, 
         t2 => new {t2.TypeId, t2.VersionId},
         (t1, t2) => new { Values = new {t1.TypeId, t1.VersionId}, Table2s = t2.DefaultIfEmpty().Where(x => x != null) })
    .Where(g => !g.Table2s.Any())
    .Select(g => g.Values)
    .ToList();

This may look a bit complicated but we essentially do an outer join between the tables on the TypeId and VersionId. When fetching the grouped result of that join we have to tell EF to exclude any cases where Table2 would be #null, otherwise we would get a collection with 1 #null element. (There may be a more optimal way to get this to work, but the above does work) This is grouped by the combination of values requested (TypeId and VersionId from Table 1). From there it is just filtering out the results where there are no Table2 records, and selecting the "Key" from the grouping, which is our desired values.

CodePudding user response：

With the help of **LINQ Except** also you can do this

Please find the following details with an example

    public class Table1
    {
        public int Id { get; set; }
        public int TypeId { get; set; }
        public int VersionId { get; set; }
        public string AnotherColumn1 { get; set; }
    }
    public class Table2
    {
        public int Id { get; set; }
        public int TypeId { get; set; }
        public int VersionId { get; set; }
        public string DifferentColumn1 { get; set; }
    }
private void GetValuesUsingExcept()
        {
            List<Table1> lstTable1 = new List<Table1>()
            {
                new Table1{Id=1, TypeId=101, VersionId=201, AnotherColumn1="Test1"},
                new Table1{Id=2, TypeId=102, VersionId=202, AnotherColumn1="Test2"},
                new Table1{Id=3, TypeId=103, VersionId=203, AnotherColumn1="Test3"}
            };

            List<Table2> lstTable2 = new List<Table2>()
            {
                new Table2{Id=1, TypeId=101, VersionId=201, DifferentColumn1="DiffVal1"},
                new Table2{Id=2, TypeId=102, VersionId=202, DifferentColumn1="DiffVal2"},
                new Table2{Id=4, TypeId=104, VersionId=204, DifferentColumn1="DiffVal3"}
            };

            var output = lstTable1.Select(s1 => new { s1.TypeId, s1.VersionId }).Except(lstTable2.Select(s2 => new { s2.TypeId, s2.VersionId })).ToList();

        }