Complex Linq query dealing with articles, categories and versioning

Question

I'm new to Linq so I appologise for the long question.

I have a CMS with articles, categories and versioning. Categories are hierarchical (i.e. Category has a ParentID) and we're using it in a slightly complicated way.

The data structure is as follows:

Category 1 with all its sub-categories (and sub-sub-categories) are considered Domains.
Category 2 with all its sub-categories are considered Knowledge Types.

Articles will be created and associated with 'n+' Domains and sub-(sub)-domains as well as 'n+' Knowledge Types.

Example:
Article Title - "Youth Development"
Domain - Development
Sub-Domain - Youth
Sub-sub-Domain - minors

Knowledge Type - Best Practice

What I need to do:
1. First extract a list of the latest version articles filtered by a given Domain
2. Second, return a list of articles grouped by Knowledge type

Because of the complexity of the table structures, i.e. multiple tables for articles (and versions), categories and article to category (versions) I'm finding it difficult to come up with a single linq query to do this.

Here's a query to get just the latest version articles:

var articleGroups = from article in _Context.Articles
                                join articleVersion in _Context.ArticleVersions
                                    on article.ArticleID equals articleVersion.ArticleID
                                join articleCategoryVersion in _Context.ArticlesCategoriesVersions
                                    on articleVersion.ArticleID equals articleCategoryVersion.ArticleID
                                where articleCategoryVersion.CategoryID == 36
                                join articleCategory in _Context.ArticleCategories
                                    on articleCategoryVersion.CategoryID equals articleCategory.CategoryID
                                group articleVersion by article.ArticleID into articleGroup
                                select articleGroup.OrderByDescending(x => x.Version).First() into articleOut
                                select new
                                {
                                    ArticleID = articleOut.ArticleID,
                                    ArticleVersion = articleOut.Version,
                                    Title = articleOut.Title
                                };

I get 1 sql query (looking at profiler), which is great! I get the articles associated with Domain 36, good.

The query looks convoluted though?

Now I just somehow need to take the resulting articles, join them with knowledge types and group them so that I can display a list of articles on a website grouped by knowledge type.

You should use navigation properties like Article.ArticleVersions. — Gert Arnold

jdweng jdweng · Accepted Answer · 2017-02-14T17:52:52

Would something like this work :

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

namespace ConsoleApplication1
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable Articles = new DataTable();
            Articles.Columns.Add("ArticleID", typeof(int));

            Articles.Rows.Add(new object[] { 1 });
            Articles.Rows.Add(new object[] { 2 });
            Articles.Rows.Add(new object[] { 3 });
            Articles.Rows.Add(new object[] { 4 });

            DataTable ArticleVersions = new DataTable();
            ArticleVersions.Columns.Add("ArticleID", typeof(int));
            ArticleVersions.Columns.Add("Version", typeof(string));
            ArticleVersions.Columns.Add("Title", typeof(string));

            ArticleVersions.Rows.Add(new object[] { 1, "a", "abc" });
            ArticleVersions.Rows.Add(new object[] { 1, "b", "def" });
            ArticleVersions.Rows.Add(new object[] { 1, "a", "ghi" });
            ArticleVersions.Rows.Add(new object[] { 1, "c", "jkl" });
            ArticleVersions.Rows.Add(new object[] { 2, "a", "mno" });
            ArticleVersions.Rows.Add(new object[] { 2, "b", "pqr" });
            ArticleVersions.Rows.Add(new object[] { 2, "a", "stu" });
            ArticleVersions.Rows.Add(new object[] { 3, "c", "vwx" });
            ArticleVersions.Rows.Add(new object[] { 4, "a", "yz" });
            ArticleVersions.Rows.Add(new object[] { 4, "b", "acd" });
            ArticleVersions.Rows.Add(new object[] { 4, "a", "ghi" });
            ArticleVersions.Rows.Add(new object[] { 4, "c", "nop" });

            DataTable ArticleCategoriesVersions = new DataTable();
            ArticleCategoriesVersions.Columns.Add("ArticleID", typeof(int));
            ArticleCategoriesVersions.Columns.Add("CategoryID", typeof(int));

            ArticleCategoriesVersions.Rows.Add(new object[] { 1,  10 });
            ArticleCategoriesVersions.Rows.Add(new object[] { 1,  11 });
            ArticleCategoriesVersions.Rows.Add(new object[] { 1,  12 });
            ArticleCategoriesVersions.Rows.Add(new object[] { 1,  21 });
            ArticleCategoriesVersions.Rows.Add(new object[] { 1,  22 });
            ArticleCategoriesVersions.Rows.Add(new object[] { 1,  35 });
            ArticleCategoriesVersions.Rows.Add(new object[] { 1,  36 });
            ArticleCategoriesVersions.Rows.Add(new object[] { 1,  37 });

            DataTable ArticleCategories = new DataTable();
            ArticleCategories.Columns.Add("CategoryID", typeof(int));
            ArticleCategories.Columns.Add("Name", typeof(string));

            ArticleCategories.Rows.Add(new object[] { 10, "article1" });
            ArticleCategories.Rows.Add(new object[] { 36, "article2" });
            ArticleCategories.Rows.Add(new object[] { 36, "article3" });
            ArticleCategories.Rows.Add(new object[] { 36, "article4" });
            ArticleCategories.Rows.Add(new object[] { 36, "article5" });
            ArticleCategories.Rows.Add(new object[] { 36, "article6" });
            ArticleCategories.Rows.Add(new object[] { 36, "article1" });

            var results = (from acv in ArticleCategoriesVersions.AsEnumerable()
                           where acv.Field<int>("CategoryID") == 36
                           join ac in ArticleCategories.AsEnumerable() on acv.Field<int>("CategoryID") equals ac.Field<int>("CategoryID")
                           join av in ArticleVersions.AsEnumerable() on acv.Field<int>("ArticleID") equals av.Field<int>("ArticleID")
                           join a in Articles.AsEnumerable() on av.Field<int>("ArticleID") equals a.Field<int>("ArticleID")
                           select new { acv = acv, ac = ac, av = av, a = a })
                           .GroupBy(x => x.av.Field<int>("ArticleID"))
                           .OrderByDescending(x => x.Key)
                           .Select(x => new {
                               Category1 = x.Where(y => y.av.Field<int>("ArticleID") == 36).Select(y => new { acv = y.acv, ac = y.ac, av = y.av, a = y.a}).ToList(),
                               Category2 = x.GroupBy(y => y.ac.Field<int>("CategoryID"), z => z).ToDictionary(y => y.Key, z => x.ToList()) 
                           }).ToList();



        }
    }



}

Complex Linq query dealing with articles, categories and versioning

1 Answers