How to Flatten a One to Many Into Comma Separated List in a Column (SQL Server)

-
This is a pretty short post, but I figure if I write it up here, I will remember it next time I need it. Every time I need this, I struggle to find it. The SQL below selects all the columns from the Claim Table, and then selects all the ErrorMessage from the ClaimValidationErrors table and creates a comma separated list that is stored in the ValidationErrors column. The STUFF command simply removes the redundant comma at the beginning of the list.

 
SELECT *,
 STUFF((SELECT ',' + [ErrorMessage]

 FROM [dbo].[ClaimValidationErrors] cve

 WHERE cve.ClaimId = c.ClaimId

 FOR XML PATH('')), 1, 1,'') ValidationErrors
 
 FROM claims c
The ('') parameter on the FOR XML PATH removes the outer xml tag. So this is actually kind of a hack but it solves the problem.
I have yet to find a way to do this in Entity Framework with linq directly, so I usually create a view and add the view to my model so I can access it from my code.

Comments

Post a Comment

Popular posts from this blog

Asp.Net Core with Extended Identity and Jwt Auth Walkthrough

-
Image
  Quite often I think of an idea for an app where I would have a website and a mobile app that share the same functionality. Every time I start to prototype something like this I get stuck on authentication. When you build a web application in Asp.Net Core, it is really easy to set up the built-in Identity platform to allow users to login, but if you want to allow a mobile app to access APIs on the same site, that takes a lot more work. Of course, you can use a cloud-based authentication system like AzureAd or Firebase authentication, but if you arent housing any sensitive data, that's a lot of extra plumbing that takes away from building your application.  This walkthrough will show you how to create an Asp.Net core site with Asp.Net Core Identity for authentication and authorization. We will be extending the identity to include additional user information and roles. We will also add jwt authentication and authorization, so SPA's and mobile apps can use the API's and share...
Read more

File Backups to Dropbox with PowerShell

-
Image
I have had a number of instances where I had a virtual server somewhere, and I wanted to have a backup strategy, but the paid plans were overkill for what I needed. I have all my source code in Github, so everything can be rebuilt. I just need to be able to archive database backups and uploaded files. I decided to use a Dropbox account and set up a scheduled task that calls a PowerShell script that accesses the Dropbox web API. Setting up Dropbox You will need to have a Dropbox subscription set up and then you can go to the developer's section at the following link: {% embed url=" https://www.dropbox.com/developers " %} On the main page, select create apps. On the following page, you will have the option to select the scope of the app. Here you can decide to only access a single folder or give access to all of the Dropbox functions. Either option will work, in my example, I used the full access scope, so I would have the option of putting different file types in different...
Read more

Dynamic Expression Builder with EF Core

-
I was recently working on an ASP.NET Core MVC project where the end user wanted to have a search form with several fields with drop down boxes and a couple of free-form text fields. One of the requirements was to be able to put "*" at the front or the end of a search term in the text boxes to indicate a wildcard search. That was the way a previous application worked and they wanted to maintain the same functionality. Since I was working with Entity Framework Core using a generic repository pattern, I wanted to be able to dynamically build the filter expression that I passed to the repository. I created a static Expression builder class with static methods for creating each type of expression needed. public static class ExpressionBuilder { public static Expression GetStringExpression ( string dataModelProperty , string dtoProperty , Expression combinedExpression , ParameterExpression parameterExpression ) { if ( dtoProperty...
Read more