Category Archives: Entity Framework

LINQ to XML using join, group by order by query

We can use LINQ to write a complex query like join and group by operations. The below sample shows a simple join query and the group by command in 2 XML files. I use the 2 xml files firstxml.xml and secondxml.xml

FirstXml.Xml

<dataroot>
  <Names>
    <NAME_ID>1121</NAME_ID>
    <PARENTS_NAME_ID>Selvan</PARENTS_NAME_ID>
  </Names>
  <Names>
    <NAME_ID>1122</NAME_ID>
    <PARENTS_NAME_ID>Sen</PARENTS_NAME_ID>
  </Names>
</dataroot>

SecondXml.Xml

<dataroot>
  <Info>
    <INFO_NAME_ID>1121</INFO_NAME_ID>
    <INFO_COMMENTS>Comment one</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 1</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 4</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 7</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 2</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 2</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 2</INFO_COMMENTS>
  </Info>
</dataroot>

 

Sample Code using LINQ

 

                XDocument first = XDocument.Load(“FirstXml.xml”);
                XDocument second = XDocument.Load(“SecondXml.xml”);

                XElement felement = first.Element(“dataroot”);
                XElement selement = second.Element(“dataroot”);

              

                var syns1 = from f in felement.Elements(“Names”)
                            join s in selement.Elements(“Info”) on f.Element(“NAME_ID”).Value equals
                            s.Element(“INFO_NAME_ID”).Value
                            where f.Element(“PARENTS_NAME_ID”).Value == “Sen”
                            orderby (string)s.Element(“INFO_COMMENTS”) descending
                            group s by s.Element(“INFO_COMMENTS”).Value into g
                            select g;

 

The above query returns the comments from second xml file matches the ID and group by the comment and order by comments descending

This version of SQL Server in use does not support datatype datetime2

I got this strange issue today, and was struggling to fix this. This issue is related to the SQL 2008 and 2005 compatibility. I had a local machine with SQL 2008, but the Dev environment is with SQL 2005. When i generated the Entities using EF in my local machine it had marked it with 2008 version. When deployed to 2005 the issue was reported.

To fix:

Open the .edmx file using the XML editor option and look for the attribute ProviderManifestToken=”2008″ and change it to 2005 and save. Build and deploy it works.

image