Saturday, June 20, 2020

Linq Query Help

This is a problem that has had me stumped for a few days, and countless searches have not lead me to a solution. This seems like a fairly basic technique, but I have had no luck with it, since I am not exactly a master using linq queries. Hopefully, this is presented in a way to clearly show what I'm after. I've chiseled-down the classes and code that I'm actually using, into a more simplified example so I can focus on the part of the solution that is causing me problems.

I am working on an older database application that uses .Net Web Forms, and the first step to a long process of revamping this application has lead me to a problem with Entity Framework 6. Actually, it's a problem with me not knowing how to do something in Entity Framework, more specifically, with a Linq query. I don't want to go too far into detail, so I'm simplifying a few of the Entity Diagrams here, then I'll ask for help in cracking the nut that I have been unable to crack.

Simplified Entities


The relationship is a one-to-many relationship between the Issues and Comments. One Issue can have many comments. My ultimate question is, how can I use a Linq query to come up with columns like this? The Comments Column is a concatenation of the CommentText Fields in each of the Comment Entities related to the Issue for each row.

Desired result


I can do this with SQL easy enough and concatenate the Comments into one column using a sub-query like so:

SQL Query with a sub-query for Comments

There are probably cleaner ways to get this done in SQL in more recent versions, but this is something I've been using since around 2007. I am considering using a Stored Procedure in my database, but I don't want to have these decisions made based on the fact that I don't know how to do something with one technology vs. the other.

Here is my attempt so far. I've created a C# class to act as a View Model


And here is part of the IssueFactory class and the function that will return the List of the IssueViewModel class. It seems like such a simple thing, and yet, here I am:


So that's it. Googling has gotten me nowhere with this for a few days. I thought I was going to be slick, and tried to call another function that I had created that built the bulleted list, but of course, linq queries can't call C# code like this, unfortunately.

So what's the piece of the puzzle that I have been missing? Anyone?

Thanks, in advance!










2 comments:

  1. Why not just model comments as part of Issues? If you look at the EF Core Docs, the getting started sample shows how to iterate "blogs" with "posts." It's the same structure as "issues" and "comments". What are you trying to do that is not addressed by this sample? https://docs.microsoft.com/ef/core/get-started/

    ReplyDelete
    Replies
    1. I had a lot of input on Reddit, and finally came up with a solution. I was putting this all in a Crystal Report...so I was trying to "flatten" everything out before passing it on to the report. I ended up using Linq to get a List, and then did a foreach on that List, and a StringBuilder to build the ultimate Comment, which is just a concat of all of the CommentText.

      Delete