CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-objects
Stephen Jones
Stephen Jones

Reputation: 98

Linq to objects Joining collections and filtering

I'm having a trouble filtering/ joining in-memory object lists with Linq to objects, and for what would normally be a trivial SQL query...

Scenario:
Have a json service(.asmx) allows client to make a DB call through Entity Framework wrapped sproc (function import). In this case a [Web Method] "GetArticles(int [] TagIds)"
I'm now looking to cache this data to save on DB calls and apply filters to the cached collection.

Data:

The web service filter param will contain an array of TagId's that returned articles can have.

So my baseline SQL query would look like so

SELECT DISTINCT a.*
FROM Article a INNER JOIN ArticlesTags at ON a.ArticleId = at.ArticleId
WHERE at.TagId in (0, 1.. 'list of tag ids')

I'm hitting a number of snags -
EF wrapped sprocs don't allow SQL 2008 (Table Value Parameters) so i can't pass in TagId list filter in the preferred format. I have found an example where two result sets can be returned from one sproc call which may get around that. So once I have the two collections (Articles and ArticlesTags) which I intend to cache, how does one join and subsequently filter based on the possible TagId's filter param with linq-to-objects?

Upvotes: 1

Views: 2940

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Here is one way to "rewrite" your SQL query in LINQ:

class Article {
    public int ArticleId;
    public string ArticleName;
    // Other fields...
}

class ArticleComparer : IEqualityComparer<Article> {
    public bool Equals(Article x, Article y) {
        return x.ArticleId == y.ArticleId && x.ArticleName == y.ArticleName;
    }
    public int GetHashCode(Article obj) {
        return obj.ArticleId.GetHashCode();
    }
}

class ArticlesTag {
    public int TagId;
    public int ArticleId;
    // Other fields...
}

class Program {

    static void Main(string[] args) {

        // Test data:

        var articles = new[] {
            new Article { ArticleId = 1, ArticleName = "Article A" },
            new Article { ArticleId = 2, ArticleName = "Article B" },
            new Article { ArticleId = 3, ArticleName = "Article C" }
        };

        var article_tags = new[] {
            new ArticlesTag { TagId = 1, ArticleId = 1 },
            new ArticlesTag { TagId = 2, ArticleId = 1 },
            new ArticlesTag { TagId = 3, ArticleId = 1 },
            new ArticlesTag { TagId = 4, ArticleId = 2 },
            new ArticlesTag { TagId = 5, ArticleId = 2 },
            new ArticlesTag { TagId = 6, ArticleId = 3 },
            new ArticlesTag { TagId = 7, ArticleId = 3 }
        };

        var tag_ids = new HashSet<int>(new[] { 2, 3, 6 });

        // JOIN "query":

        var q = (
            from article in articles
            join article_tag in article_tags
                on article.ArticleId equals article_tag.ArticleId
            where tag_ids.Contains(article_tag.TagId)
            select article
        ).Distinct(new ArticleComparer());

        foreach (var article in q)
            Console.WriteLine(
                string.Format(
                    "ArticleId = {0}\tArticleName = {1}",
                    article.ArticleId,
                    article.ArticleName
                )
            );

    }

}

This prints:

ArticleId = 1   ArticleName = Article A
ArticleId = 3   ArticleName = Article C

Upvotes: 1

Despertar
Despertar

Reputation: 22372

So you have an Article table, a Tags table, and an ArticleTags table that associates an Articles ID with a tags ID. Article name is the easy part. Then you go through the ArticleTags and get all the TagID's that match that articles ID, then you go through the Tags to get the name that is associated with that tag ID. Turns it into one neat dictionary with a TagName for the key and the value is an IEnumerable< string > of TagNames. (you could pull the whole Article and the whole Tag for a Dictionary< Article, Tag > instead of Dictionary< string, string > just leave out the member selector.

var articleDictionary = articles.ToDictionary(
       a => a.Name, // article name is key
       a => ArticleTags
             .where(t => t.ArticleID == a.Id)
             .select(t => Tags.Single(tag => tag.id == t.id).TagName // value is an IEnumerable<string> of tag names
)

foreach (var article in articleDictionary)
{
    Console.WriteLine(article.Key);
    foreach (var tag in article.Value)
    { 
        Console.WriteLine("\t" + tag)
    }
}

Edit: Here's how you would pull the whole objects

var articleDictionary = articles.ToDictionary(
       a => a,
       a => ArticleTags
             .where(t => t.ArticleID == a.Id)
             .select(t => Tags.Single(tag => tag.id == t.id)
)

Upvotes: 1

Related Questions