Sql For Xml on related tables


Keywords: Sql, Xml, C#, Linq, ORM, Entity Framework
Publish Date: 3/7/2014
Description: Get related sql records as a single string property

I'm writing a band manager application and am only using stored procedures with EntityFramework instead of dynamically created SQL using the Linq syntax against the EF Model.

Why? - Mostly just for fun and experience but I really like fast applications and don't like to leave performance on the table. Also after I finished my code generator, it was trivial to produce the majority of the boiler plate data access code.

Problem scope:

Get related table records as a property/column of the stored procedure and keep it to one record per main table.

In my case I want a list of payments and that payments related event and performer info

band-manager-schema

This has the data but not exactly in the way I want.

sql for xml

Solution:

As usual Stackoverflow to the rescue:

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

Lets compare what LINQ over EF creates for us. There are more ways to slice this but I think it's close enough to compare. What is interesting is that EF still returns two records and it must transform that into the C# object that I asked for.

EF generated SQL

More info on For Xml

http://technet.microsoft.com/en-us/library/ms178107.aspx

Since I solved my problem I'm moving on but I'm sure there is more fun to be had with For XML.

Comments (0):

Leave a Comment:




    Captcha Challenge