how to get multiple result sets using linq

Question

I have a stored procedure with around 14 different result sets. How do I retrieve them all as by now I only get the first result set.

[HttpGet]
[Route("tire-tabel")]
public List<DeviationCalculation_Result> TireTabel(decimal presentWidth, decimal presentAspectRatio, string presentRimSize, int maxDeviation)
{
    using (var context = new OminiTireEntities())
    {
        var result = context.Database.SqlQuery<DeviationCalculation_Result>(
"exec [Tabel].[DeviationCalculation] @PresentWidth = '" + presentWidth + "', " +
"@PresentAspectRatio= '" + presentAspectRatio + "', " +
"@PresentInches= '" + presentRimSize + "', " +
"@MaxDeviation= '" + maxDeviation + "'").ToList<DeviationCalculation_Result>();
        return result;
    }
}

What is the code inside [Tabel].[DeviationCalculation]? You are passing few parameter values which I think gets into your where condition and returns you the filtered result? — Souvik Ghosh
– Souvik Ghosh, Commented Sep 4, 2017 at 12:13
not exactly it goes in to mathematical functions for calculating the circumference and then give back the deviation of 15 predefined circumference — Kewin Björk Nielsen
– Kewin Björk Nielsen, Commented Sep 4, 2017 at 12:21

Ramesh · Accepted Answer · 2017-09-04 12:11:12Z

Sample Code:

using (var db = new BloggingContext())
{
    // If using Code First we need to make sure the model is built before we open the connection
    // This isn't required for models created with the EF Designer
    db.Database.Initialize(force: false);

    // Create a SQL command to execute the sproc
    var cmd = db.Database.Connection.CreateCommand();
    cmd.CommandText = "[dbo].[GetAllBlogsAndPosts]";

    try
    {

        db.Database.Connection.Open();
        // Run the sproc 
        var reader = cmd.ExecuteReader();

        // Read Blogs from the first result set
        var blogs = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);   


        foreach (var item in blogs)
        {
            Console.WriteLine(item.Name);
        }        

        // Move to second result set and read Posts
        reader.NextResult();
        var posts = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Post>(reader, "Posts", MergeOption.AppendOnly);


        foreach (var item in posts)
        {
            Console.WriteLine(item.Title);
        }
    }
    finally
    {
        db.Database.Connection.Close();
    }
}

The Translate method accepts the reader that we received when we executed the procedure, an EntitySet name, and a MergeOption. The EntitySet name will be the same as the DbSet property on your derived context. The MergeOption enum controls how results are handled if the same entity already exists in memory.

Reference : https://msdn.microsoft.com/en-us/library/jj691402(v=vs.113).aspx

I also recommend to use Parameters instead of executing the queries as mentioned in the question as it can result in SQL injection

peco · Accepted Answer · 2017-09-04 13:42:13Z

With Dapper it is super simple:

public DeviationCalculationResult Get(decimal presentWidth, decimal presentAspectRatio, string presentRimSize, int maxDeviation)
{
    using (var context = new OminiTireEntities())
    {
        var reader = context.Database.Connection.QueryMultiple("[Tabel].[DeviationCalculation]",
            new
            {
                PresentWidth = presentWidth,
                PresentAspectRatio = presentAspectRatio,
                PresentInches = presentRimSize,
                MaxDeviation = maxDeviation
            }, commandType: CommandType.StoredProcedure);

        var first = reader.Read<First>().ToList().First();
        var second = reader.Read<Second>().ToList().First();
        var third = reader.Read<Third>().ToList().First();
        //...and so on...

        return new DeviationCalculationResult
        {
            First = first,
            Second = second,
            Third = third,
            //...
        };
    }
}

Collectives™ on Stack Overflow

how to get multiple result sets using linq

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related