How To Convert Xml Output From Stored Procedure To C# Object In Asp Net Core

SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO ALTER PROC [dbo].[getStateWiseCompanyDetails] AS BEGIN With Data AS (SELECT Companies.RegionId, Companies.Co

Solution 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")]
publicclassCompanyList
{
    [XmlElement(ElementName = "RegionId")]
    publicstring RegionId { get; set; }
    [XmlElement(ElementName = "Code")]
    publicstring Code { get; set; }
    [XmlElement(ElementName = "CompanyName")]
    publicstring CompanyName { get; set; }
    [XmlElement(ElementName = "FirstName")]
    publicstring FirstName { get; set; }
    [XmlElement(ElementName = "OfficePhone")]
    publicstring OfficePhone { get; set; }
}

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

Second, you should create converter class:

publicclassObjectToDbStringEquivalentConvertor<T> : ValueConverter<T, string>
{
    publicObjectToDbStringEquivalentConvertor(ConverterMappingHints mappingHints = null) : base(convertToProviderExpression, convertFromProviderExpression, mappingHints)
    { }

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

    publicstaticstringToDbString<T>(T obj)
    {

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

     }

    publicstatic 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
        {
            thrownew Exception();
        }
  
    }



}

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

publicclassOrganization
{
    [Key]
    publicint OrgID { get; set; }
    public StateWiseCompany OrgName { get; set; }
}

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

protectedoverridevoidOnModelCreating(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: