LINQ JOIN with different data types

Question

I have a situation where I am trying to do a join on 2 tables.

Table one has the following:

 User       ID
 john       1 
 mary       3
 tim        4

Table 2 has the following: (Note that ID in table 2 is comma delimited. Note that each ID in table 2 is unique. )

company         ID
 100           '150', '4', '9'   
 101           '102','2', '10'

as you can see in the first row of table 2 there is a match with user tim as they both have 4 in them so company 100 is associated.

Issue is with LINQ I am not sure how to do the join between these 2 tables.

Is this an existing system or something new? If new, why not go with an intermediate mapping table for this inherently many-to-many relationship? — Anthony Pegram, Apr 06 '17 at 17:49
Your best bet may be to create a view that better normalizes the data in Table 2. — krillgar, Apr 06 '17 at 17:51

score 0 · Answer 1 · answered Apr 06 '17 at 20:34

You can split your companies table ids and format them into more readable list :

var companiesReadableList =
            companies.SelectMany(x => x.ID.Replace("'", "").Split(',').Select(y => new {ID = y.Trim(), company = x.company}));

all that's left is to join user table :

var joinedView = users.Join(companiesReadableList, user => user.ID.ToString(), company => company.ID,
            (user, company) => new {user.user, user.ID, company.company});

hope it helps

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

try something like using like clause with single quotations

var res = from Table1
join Table2 
on SqlMethods.Like(Table1.Id , '\'' + Table2.Id + '\'')
select ....

i can't test this but i based my answer on this

should work if linq-to-sql

edited May 23 '17 at 12:32

Community

1
1

answered Apr 06 '17 at 22:48

Modar Na

873
7
18

LINQ JOIN with different data types

2 Answers2