Linq Queryable - Joining two tables with no relationships

Question

Firstly, this is for a legacy application, so I cannot radically change any logic.

I have database with two tables that do not have any relationships. I asked if I change this, but was told I cannot.

These tables can be described as

Create Table T1
    [doc_id] [int] NOT NULL,                   -- Primary Key
    [customer_acc_no] [varchar](16) NULL,
    [gcompany] [varchar](30) NULL,
    ....
    extra fields

and table

Create Table T2
    [UserURN] [int] NOT NULL,                  -- All three fields make up
    [AccountNumber] [varchar](20) NOT NULL,    -- the primary key
    [Company] [varchar](50) NOT NULL,
    ....
    extra fields

As you can see, not only are the field names different, but they have different lengths too.

I am using the Repository and UnitOfWork patterns. So far I have managed to code the following:

private IRepository<T1> _t1Repository;
private IRepository<T2> _t2Repository;

These are populated within the constructor.

Next I use the following code to configure get a queryable repository.

var retVal = _t1Repository.Queryable();

From this, I am trying to add the following join.

from q in T1
join w in T2
on new { X1 = q.gcompany, X2 = q.Customer_acc_no } 
equals new { X1 = w.Company, X2 = w.AccountNumber }

I am thinking it would be along the lines of:

var query = T1.GroupJoin(T2,
        c => c.gcompany,
        o => o.Company,
        (c, result) => new Result(c.doc_id, result))
    .GroupJoin(T2,
        c => c.Customer_acc_no,
        o => o.AccountNumber ,
        (c, result) => new Result(c.doc_id, result));

but i'm not sure as all attempts so far end in errors within visual studio.

Just to pitch an idea, if all 3 fields make up 1 key, you can create a new class containing those fields and create a custom comparer for said class. — , Jan 12 '17 at 15:05
I'll refer to [this](http://stackoverflow.com/questions/634826/using-an-object-as-a-generic-dictionary-key) question/answer. It talks about using a class as a key for a dictionary, but I'm pretty sure it's applicable for your case. — , Jan 12 '17 at 15:14
I may have missed the point after looking at the link supplied, but I am using the repository pattern, not a dictionary lookup so am unsure of what you are proposing. sorry. — gilesrpa, Jan 12 '17 at 15:19
What's the issue? You started well with LINQ query syntax `from q in T1 join w in T2 ...`, why not continue that query instead of trying `GroupJoin` and similar method syntax queries. — Ivan Stoev, Jan 12 '17 at 18:50

score 1 · Accepted Answer · answered Jan 12 '17 at 15:29

See code below :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication42
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("doc_id", typeof(int));
            dt1.Columns.Add("customer_acc_no", typeof(string));
            dt1.Columns.Add("gcompany", typeof(string));

            dt1.Rows.Add(new object[] { 1, "100", "abc" });
            dt1.Rows.Add(new object[] { 2, "100", "def" });
            dt1.Rows.Add(new object[] { 3, "100", "def" });
            dt1.Rows.Add(new object[] { 4, "101", "abc" });
            dt1.Rows.Add(new object[] { 5, "101", "ghi" });
            dt1.Rows.Add(new object[] { 6, "102", "jkl" });
            dt1.Rows.Add(new object[] { 7, "102", "abc" });
            dt1.Rows.Add(new object[] { 8, "102", "def" });
            dt1.Rows.Add(new object[] { 9, "103", "abc" });
            dt1.Rows.Add(new object[] { 10, "103", "abc" });


            DataTable dt2 = new DataTable();
            dt2.Columns.Add("UserURN", typeof(int));
            dt2.Columns.Add("AccountNumber", typeof(string));
            dt2.Columns.Add("Company", typeof(string));

            dt2.Rows.Add(new object[] { 11, "100", "abc" });
            dt2.Rows.Add(new object[] { 12, "100", "def" });
            dt2.Rows.Add(new object[] { 13, "100", "def" });
            dt2.Rows.Add(new object[] { 14, "101", "abc" });
            dt2.Rows.Add(new object[] { 15, "101", "ghi" });
            dt2.Rows.Add(new object[] { 16, "102", "jkl" });
            dt2.Rows.Add(new object[] { 17, "102", "abc" });
            dt2.Rows.Add(new object[] { 18, "102", "def" });
            dt2.Rows.Add(new object[] { 19, "103", "abc" });
            dt2.Rows.Add(new object[] { 20, "103", "abc" });

            var results = from r1 in dt1.AsEnumerable()
                          join r2 in dt2.AsEnumerable() on
                             new { x1 = r1.Field<string>("customer_acc_no"), x2 = r1.Field<string>("gcompany") } equals
                             new { x1 = r2.Field<string>("AccountNumber"), x2 = r2.Field<string>("Company") }
                          select new { t1 = r1, t2 = r2 };
        }
    }
}

It works, but it is not using the repository pattern – gilesrpa Jan 12 '17 at 15:37 — gilesrpa, Jan 12 '17 at 15:37

Linq Queryable - Joining two tables with no relationships

1 Answers1