How to Convert XML output from Stored Procedure to C# object in Asp Net Core

Question

SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO ALTER PROC
[dbo].[getStateWiseCompanyDetails] AS
BEGIN
With Data
     AS (SELECT Companies.RegionId,
                Companies.Code,
                Companies.CompanyName,
                Users.FirstName,
                Companies.OfficePhone
         FROM Companies
              INNER JOIN Users ON Companies.DirectorId = Users.Id)
     SELECT States.Id AS RegionId,
            States.StateName,
            States.IsRegion AS Status,
            Users.FirstName + ' ' + Users.LastName AS RegionDirector,
            Users.PhoneNumber,
     (
         SELECT *
         FROM Data
         WHERE data.RegionId = States.Id FOR XML PATH('CompanyList'), ROOT('StateWiseCompany'), TYPE
     ) AS CompanyList
     FROM States
          INNER JOIN Users ON States.RegionDirector = Users.Id;
END;

Actual Output Of Stored Procedure

Last Column That will contain These information

I would like to call this stored procedure in my dot net Core Web application.

Answer 1

According to your description, if you have already used EF core to create the database and could receive the Stored Procedure data by using _dbContext.<yourdbsetclass>.FromSqlRaw method. I suggest you could try to create a custom ValueConverter for EF to achieve your requirement.

The valueconvert could convert the string to object when you get the data from sqldatbase by using EF core and will convert the object to string when you want to insert the data into database.

More details, you could refer to below codes:

First, you should create StateWiseCompany class

[XmlRoot(ElementName = "CompanyList")]
public class CompanyList
{
    [XmlElement(ElementName = "RegionId")]
    public string RegionId { get; set; }
    [XmlElement(ElementName = "Code")]
    public string Code { get; set; }
    [XmlElement(ElementName = "CompanyName")]
    public string CompanyName { get; set; }
    [XmlElement(ElementName = "FirstName")]
    public string FirstName { get; set; }
    [XmlElement(ElementName = "OfficePhone")]
    public string OfficePhone { get; set; }
}

[XmlRoot(ElementName = "StateWiseCompany")]
public class StateWiseCompany
{
    [XmlElement(ElementName = "CompanyList")]
    public List<CompanyList> CompanyList { get; set; }
}

Second, you should create converter class:

public class ObjectToDbStringEquivalentConvertor<T> : ValueConverter<T, string>
{
    public ObjectToDbStringEquivalentConvertor(ConverterMappingHints mappingHints = null) : base(convertToProviderExpression, convertFromProviderExpression, mappingHints)
    { }

    private static Expression<Func<T, string>> convertToProviderExpression = x => ToDbString(x);
    private static Expression<Func<string, T>> convertFromProviderExpression = x => ToObject<T>(x);

    public static string ToDbString<T>(T obj)
    {

        using (var stringwriter = new System.IO.StringWriter())
        {
            var serializer = new XmlSerializer(typeof(T));
            serializer.Serialize(stringwriter, obj);
            return stringwriter.ToString();
        }

     }

    public static T ToObject<T>(string stringValue)
    {
        if (stringValue != string.Empty)
        {
            using (var stringReader = new System.IO.StringReader(stringValue))
            {
                var serializer = new XmlSerializer(typeof(T));
                var re = serializer.Deserialize(stringReader);
                return (T)re;
            }
        }
        else
        {
            throw new Exception();
        }
  
    }



}

Thirdly, you should modify your dbcontext model's property type from string to object.

public class Organization
{
    [Key]
    public int OrgID { get; set; }
    public StateWiseCompany OrgName { get; set; }
}

Forth, I suggest you should modify the dbcontext to override the OnModelCreating method like below:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Organization>(
            entity => {
                entity.HasKey(e => e.OrgID);
                entity.Property(e => e.OrgName).HasConversion(new ObjectToDbStringEquivalentConvertor<StateWiseCompany>());
                }
           
            ) ;

    }

At last, you could call the SP by using EF core dbcontext:

        var re = _dbContext.Organizations.FromSqlRaw("EXEC [dbo].[getStateWiseCompanyDetails]").ToList();

Result:

Database:

Query result: