There are two sql tables from which I want combined data

1. queryTable --- here queryFrom is number and QueryTo is string of comma separated numbers

2. userTable

I am able get data for queryFrom where there is single user but not for queryTo, I want to split the string {","}, convert to int array, search in userTable for that number and join back with respective username.

here is my query

 var query = (from q in queryTable
              select {
              Id  = q.id,
              QueryFrom = userTable.where(u=> u.id == q.queryFrom).select(s=>s.userName),
              QueryTo = q.queryTo // split and join logic here
              }).ToList();

Current output ---

expected output ---

CodePudding user response：

Given your data:

var userTable = new[]
{
    new { UserId = 1, UserName = "Tom" },
    new { UserId = 2, UserName = "Mike" },
    new { UserId = 3, UserName = "Harry" },
};

var queryTable = new[]
{
    new { QueryId = 1, QueryFrom = 1, QueryTo = "2, 3" },
    new { QueryId = 2, QueryFrom = 2, QueryTo = "3" },
    new { QueryId = 2, QueryFrom = 3, QueryTo = "1, 2" },
};

Here is the cleanest way I can think of doing this:

var query =
(
    from q in queryTable
    join uf in userTable on q.QueryFrom equals uf.UserId
    let uts =
        from qt in q.QueryTo.Split(',')
        join ut in userTable on int.Parse(qt.Trim()) equals ut.UserId
        select ut.UserName
    select new
    {
        q.QueryId,
        QueryFrom  = uf.UserName,
        QueryTo = String.Join(", ", uts),
    }
).ToList();

That gives me:

CodePudding user response：

i have tested with this piece of code:

1. classes defintion

public class queryTable
{
    public queryTable(int queryId, int queryFrom, string queryto)
    {
        QueryId = queryId;
        QueryFrom = queryFrom;
        Queryto = queryto;
    }

    public int QueryId { get; set; }
    public int QueryFrom { get; set; }
    public string Queryto { get; set; }
}
public class userTable
{
    public userTable(int userId, string userName)
    {
        UserId = userId;
        UserName = userName;
    }

    public int UserId { get; set; }
    public string UserName { get; set; }
}

var queryTable = new List<queryTable> { new queryTable(1, 1, "2,3"), new queryTable(2, 2, "3"), new queryTable(3, 3, "1,2") };
var userTable = new List<userTable> { new userTable(1, "Tom"), new userTable(2, "Mike"), new userTable(3, "Harry") };

var query = 
(from q in queryTable
    select (new {
    Id = q.QueryId,
    QueryFrom = userTable.Where(u => u.UserId == q.QueryId).Select(s => s.UserName).FirstOrDefault(),
    QueryTo = string.Join(",", q.Queryto.Split(',')   //split
                                .Select(n => userTable.Where(u => u.UserId == int.Parse(n.Trim())) 
                                .Select(u => u.UserName).FirstOrDefault())
                         )
                })).ToList();
foreach (var l in query)
    Console.WriteLine(l);

result:

{ Id = 1, QueryFrom = Tom, QueryTo = Mike,Harry }
{ Id = 2, QueryFrom = Mike, QueryTo = Harry }
{ Id = 3, QueryFrom = Harry, QueryTo = Tom,Mike }