Pivot table in Linq

Question

I know you cannot cannot directly use the tSQL PIVOT function in Linq, but I cannot get the correct Linq syntax for what I feel is a simple transform (which I can do in straight tSQL)

I need to take this data set:

and pivot it into this:

Can someone please help me with the correct Linq syntax?

Here's how I can accomplish this in tSQL (both using PIVOT() and not)

 Select piv.* from (
  select custom_data_key, custom_data_value from dbo.kb_article_custom_data cd
 inner join dbo.kb_article kb on cd.article_id = kb.article_id
 where (custom_data_key='article_problem' or custom_data_key = 'article_cause' or custom_data_key='article_solution') and article_number='AKB26'
 ) d
 pivot
 (max(custom_data_value) for custom_data_key in([article_problem],[article_cause], [article_solution])) piv;

--WITHOUT USING PIVOT()

select 
 max(case when t.[custom_data_key]='article_problem' then t.[custom_data_value] end) as Article_problem,
  max(case when t.[custom_data_key]='article_cause' then t.[custom_data_value] end) as Article_cause,
   max(case when t.[custom_data_key]='article_solution' then t.[custom_data_value] end) as Article_solution
 from(select custom_data_key, custom_data_value from dbo.kb_article_custom_data cd
 inner join dbo.kb_article kb on cd.article_id = kb.article_id
 where (custom_data_key='article_problem' or custom_data_key = 'article_cause' or custom_data_key='article_solution') and article_number='AKB26')t

This LINQ statement will get me the results in the first image above:

var query = 
                        from a in custdata
                        join b in kbase on a.article_id equals b.article_id
                        where (a.custom_data_key == "article_problem" || a.custom_data_key == "article_cause" || a.custom_data_key == "article_solution") && b.article_number == id
                        select new { Key = a.custom_data_key, Value = a.custom_data_value };

Here's what is failing. I take the results of the Linq query above-

var q2 = from row in  query
                     group row by "Value" into g
                     select new TO_Kbase
                     {
                         Problem= g.Where(c => c.Key =="article_problem" ).Select(c => c.Value).ToString(),
                         Cause = g.Where(c => c.Key =="article_cause").Select(c => c.Value).ToString(),
                         Solution = g.Where(c => c.Key =="article_solution").Select(c => c.Value).ToString()
                     };
            foreach(var x in q2)
            {
                TO_Kbase kb = new TO_Kbase();
                kb.Problem =x.Problem;
                kb.Cause = x.Cause;
                kb.Solution = x.Solution;
                ta.Add(kb);

            }

The output is this:

Problem: System.Data.Linq.SqlClient.Implementation.ObjectMaterializer`1+d__0`1[System.Data.SqlClient.SqlDataReader,System.String] 
Cause: System.Data.Linq.SqlClient.Implementation.ObjectMaterializer`1+d__0`1[System.Data.SqlClient.SqlDataReader,System.String] 
Solution: System.Data.Linq.SqlClient.Implementation.ObjectMaterializer`1+d__0`1[System.Data.SqlClient.SqlDataReader,System.String]

Answer 1

If I understood your data structure correctly, you could do something very similar to your "WITHOUT USING PIVOT()" T-SQL query:

// Test data.
var kb_article_custom_data = new CustomData[] {
    new CustomData() { article_id = 1, custom_data_key = "article_problem", custom_data_value = "when you try ... 1"},
    new CustomData() { article_id = 1, custom_data_key = "article_cause", custom_data_value = "the issues may occur ... 1"},
    new CustomData() { article_id = 1, custom_data_key = "article_solution", custom_data_value = "1. Click start, then ... 1"},
    new CustomData() { article_id = 2, custom_data_key = "article_problem", custom_data_value = "when you try ... 2"},
    new CustomData() { article_id = 2, custom_data_key = "article_cause", custom_data_value = "the issues may occur ... 2"},
    new CustomData() { article_id = 2, custom_data_key = "article_solution", custom_data_value = "1. Click start, then ... 2"},
    new CustomData() { article_id = 3, custom_data_key = "article_problem", custom_data_value = "when you try ... 3"},
    //new CustomData() { article_id = 3, custom_data_key = "article_cause", custom_data_value = "the issues may occur ... 3"},
    new CustomData() { article_id = 3, custom_data_key = "article_solution", custom_data_value = "1. Click start, then ... 3"},
};

var kb_article = new Article[] {
    new Article() { article_id = 1, article_title = "Title ... 1"},
    new Article() { article_id = 2, article_title = "Title ... 2"},
    new Article() { article_id = 3, article_title = "Title ... 3"},
};

// Query resembling your "without pivot" query.
var result =
    from article in kb_article
    join custom in kb_article_custom_data on article.article_id equals custom.article_id into ac
    select new {
        id = article.article_id,
        title = article.article_title,
        problem = ac.Where(x => x.custom_data_key == "article_problem").Select(x => x.custom_data_value).FirstOrDefault(x => x != null),
        cause = ac.Where(x => x.custom_data_key == "article_cause").Select(x => x.custom_data_value).FirstOrDefault(x => x != null),
        solution = ac.Where(x => x.custom_data_key == "article_solution").Select(x => x.custom_data_value).FirstOrDefault(x => x != null)
    };

foreach (var r in result)
    Console.WriteLine(r);

Which produces the following output:

{ id = 1, title = Title ... 1, problem = when you try ... 1, cause = the issues may occur ... 1, solution = 1. Click start, then ... 1 }
{ id = 2, title = Title ... 2, problem = when you try ... 2, cause = the issues may occur ... 2, solution = 1. Click start, then ... 2 }
{ id = 3, title = Title ... 3, problem = when you try ... 3, cause = , solution = 1. Click start, then ... 3 }

If you want to filter by a specific article_id, you need to add a where clause:

var id = 1;
var result =
    from article in kb_article
    where article.article_id == id
    join custom in kb_article_custom_data on article.article_id equals custom.article_id into ac
    select new
    {
        id = article.article_id,
        title = article.article_title,
        problem = ac.Where(x => x.custom_data_key == "article_problem").Select(x => x.custom_data_value).FirstOrDefault(x => x != null),
        cause = ac.Where(x => x.custom_data_key == "article_cause").Select(x => x.custom_data_value).FirstOrDefault(x => x != null),
        solution = ac.Where(x => x.custom_data_key == "article_solution").Select(x => x.custom_data_value).FirstOrDefault(x => x != null)
    };

Producing the following output:

{ id = 1, title = Title ... 1, problem = when you try ... 1, cause = the issues may occur ... 1, solution = 1. Click start, then ... 1 }

Answer 2

Solution:

 var query =
                        from a in custdata
                        join b in kbase on a.article_id equals b.article_id into ac
                        where (a.custom_data_key == "article_problem" || a.custom_data_key == "article_cause" || a.custom_data_key == "article_solution") && a.article_id == id
                        group a by new { a.article_id} into abc
                        select new
                        {
                            ID = abc.Key.article_id,
                            Cause = abc.Where(a =>a.custom_data_key == "article_cause").Select(a => a.custom_data_value).FirstOrDefault(x => x != null),
                            Problem = abc.Where(a => a.custom_data_key == "article_problem").Select(a => a.custom_data_value).FirstOrDefault(x => x != null),
                            Solution = abc.Where(a => a.custom_data_key == "article_solution").Select(a => a.custom_data_value).FirstOrDefault(x => x != null)
                        };