CODEWITHSUNDEEP
c#linqentity-framework-core
dev99
dev99

Reputation: 143

Split, convert to number then join back to string in linq c#

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

enter image description here

  1. userTable

enter image description here

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 ---

enter image description here

expected output ---

enter image description here

Upvotes: 0

Views: 724

Answers (3)

Frenchy
Frenchy

Reputation: 17007

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 }

following the error you have: try to compare string and not int

.Select(n => userTable.Where(u => u.UserId.toString() == n.Trim())

Upvotes: 1

pjs
pjs

Reputation: 383

The difficulty here is because it's an invalid database design. Columns should have single values, not a list of values, and this example shows why. If you replace the existing multi-valued QueryTo column with separate rows, with integer columns QueryFrom and QueryTo, the data manipulation becomes trivial.

Query table: QueryId int, QueryFrom int QueryTo table: QueryId int, QueryTo int (one row for each user being queried)

Upvotes: 0

Enigmativity
Enigmativity

Reputation: 117027

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:

query

Upvotes: 1

Related Questions