Make a usable Join relationship with LINQ on top of a database CSV design error
- by jdk
I'm looking for a way to fix and/or abstract away a comma-separated values (CSV) list in a database field in order to reconstruct a usable relationship such that I can properly join the two tables below and query them using LINQ and its Join method.
Following is a sample showing the Person table and CsvArticleIds field having a CSV value to represent a one-to-many association with Article records.
TABLE [dbo].[Person]
Id Name CsvArticleIds
-- ---------- --------
1 Joe "15,22"
5 Ed "22"
10 Arnie "8,15,22"
^^^(Of course a link table should have been created; nonetheless the relationship with articles is trapped inside that list of CSV values.)
TABLE [dbo].[Article]
Id Title
-- ----------
8 Beginning C#
15 A Historic look at Programming in the 90s
22 Gardening in January
Additional Info
the fix can be at any level: C#.NET or SQL Server
something easy because I will be repeating the solution for many other CSV values in other tables.
Elegant is nice too.
not looking for efficiency because this is part of a one-time data migration task and can take as long as it wants to run.