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


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

sql for xml


As usual Stackoverflow to the rescue:


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


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