Combine two list using Linq and add data as needed from different tables

Question

I need to change a process and have been struggling with it for a couple of days now. The current task checks for all digits entered by the user in Table1. I don't have an issue with that since I can return it with this statement:

var itemsTable1 = db.Table1.Where(a =>
    searchNumbers.Contains(a.Digit1) || searchNumbers.Contains(a.Digit2) || searchNumbers.Contains(a.Digit3) ||
    searchNumbers.Contains(a.Digit4) || searchNumbers.Contains(a.Digit5) || _Digit6 == a.Digit6 && a.ValidFlag == 1
).ToList();

Now I need to look for the same digits on Table2 and make sure I bring those numbers as well. Although the tables will have the same columns for digits, they will not have the same number of columns in total. I could just right another statement as above for Table2, no problem there. However, I also need to bring the records that do not contain the digits but have the same Ids. So, my scenarios would be something like this:

Table1 = contains digits -> Table2 != contains digits
Table2 = contains digits -> Table1 != contains digits
Table1 = contains digits -> Table2 = contains digits

Finally, I need to display the data on either list in a descending order, which I assume, I'd would have to combine the two/three lists and return it to the model.

Is there a way of doing this with plain Linq? Or am I better off creating maybe a CTE in a stored procedure and pass the parameters there and then calling in the EF?

Answer 1

Following @Svyatoslav Danyliv suggestion. I have created the following:

 //By using the list, we make sure that the search returns every single digit, regardless of position they occupy in the DB
                        var itemsT1 = db.Table1.Where(a => searchNumbers.Contains(a.Digit1) || searchNumbers.Contains(a.Digit2) || searchNumbers.Contains(a.Digit3) ||
                                           searchNumbers.Contains(a.Digit4) || searchNumbers.Contains(a.Digit5) || _Digit6 == a.Digit6 && a.ValidDrawResults == 1);
                               

                        var itemsT2 = db.Table2.Where(a => searchNumbers.Contains(a.Digit1) || searchNumbers.Contains(a.Digit2) || searchNumbers.Contains(a.Digit3) ||
                                           searchNumbers.Contains(a.Digit4) || searchNumbers.Contains(a.Digit5) || _Digit6 == a.Digit6 && a.ValidDrawResults == 1);                                   

                        //Create list to hold Ids from the records above
                        List<int?> t1Ids = new List<int?>();
                        List<int?> t2Ids = new List<int?>();

                        //Insert the Ids into the lists
                        foreach (var t1Id in t1Ids )
                        {
                            t1Ids.Add((int)t1Id.Id);
                        }

                        foreach (var t2Id in t2Ids)
                        {
                            t2Ids.Add((int)t2Id.Id);
                        }

                        //Get the records from opposite table that contains same Ids
                        var resultT1 = db.Table1.Where(r => t1Ids.Contains(r.Id)
                                                        );

                        var resultT2 = db.Table2.Where(r => t2Ids.Contains(r.Id)
                                                        );

                        //Combine the lists to pass to the view
                        var groupedT1 = itemsT1.Concat(resultT1).Distinct();
                        var groupedT2 = itemsT2.Concat(resultT2).Distinct();

                        using (db)
                        {
                            var vmT1T2 = new ViewModelTables
                            {
                                getTable1 = groupedT2.ToList(),
                                getTable2 = groupedT2.ToList()
                            };
                            return View(vmT1T2);
                        }

It worked out perfectly as far as bring the records that I needed. Once again, thank you @Svyatoslav Danyliv for pointing me in the right direction. I appreciate and hope this can help someone else as well.

Answer 2

I assume you need this query:

var query = 
    from t1 in db.Table1
    join t2 in db.Table2 on t1.Id equals t1.Id
    let t1Contains = searchNumbers.Contains(t1.Digit1) 
                || searchNumbers.Contains(t1.Digit2) 
                || searchNumbers.Contains(t1.Digit3) 
                || searchNumbers.Contains(t1.Digit4) 
                || searchNumbers.Contains(t1.Digit5) 
                || _Digit6 == t1.Digit6 && t1.ValidFlag == 1
    let t2Contains = searchNumbers.Contains(t2.Digit1) 
                || searchNumbers.Contains(t2.Digit2) 
                || searchNumbers.Contains(t2.Digit3) 
                || searchNumbers.Contains(t2.Digit4) 
                || searchNumbers.Contains(t2.Digit5) 
                || _Digit6 == t2.Digit6 && t2.ValidFlag == 1
    where t1Contains != t2Contains || t1Contains && t2Contains
    select 
    {
        t1,
        t2
    };

Note, that you have not specified desired output and how to order result.