Combine result from different stored procedure into a single list in c#

Question

I am trying to join two different list together using the Concat Method as shown below. I need the second list to be added to the first list, so that it can come as one lIst.

var term = dc.AgentTerm(Agentcode).ToList();
var hosp = dc.AgentHospCashPlan(Agentcode).ToList();
var life = term.Concat(hosp).ToList();

But when i tried it, I get this error.

Error 16 Instance argument: cannot convert from System.Collections.Generic.List<IbsMobile.AgentTerm_Result> to System.Linq.IQueryable<IbsMobile.AgentHospCashPlan_Result> C:\Project\IbsMobile\IbsMobile\Controllers\ParamController.cs 506 24 IbsMobile

Error 17 System.Collections.Generic.List<IbsMobile.AgentTerm_Result> does not contain a definition for 'Concat' and the best extension method overload System.Linq.Queryable.Concat<TSource>(System.Linq.IQueryable<TSource>, System.Collections.Generic.IEnumerable<TSource>) has some invalid arguments C:\Project\IbsMobile\IbsMobile\Controllers\ParamController.cs 506 24 IbsMobile

I checked online and all I see is how to convert 'System.Linq.IQueryable' to 'System.Collections.Generic.IList'.

The results that I'm assigning to term and Hosp are from stored procedures.

I have used the concat method before on lists with the same class and it worked fine.

I am guessing this error is because the results sets are from different classes.

I know this is a novice question but I'm not sure exactly what I'm doing wrong.

Answer 1

You cannot Concat lists of two different source types. See functions signature:

public static System.Collections.Generic.IEnumerable<TSource> Concat<TSource> 
    (this System.Collections.Generic.IEnumerable<TSource> first, 
     System.Collections.Generic.IEnumerable<TSource> second);

To still do so project each list into some type that share the same base type or interface. Something of the structure:

public interface IInterface {}
public class AgentTermDTO : IInterface {}
public class AgentHospCashPlanDTO : IInterface {}

or a quick and dirty solution (don't go for this one) is to cast each to a collection of objects: list1.Cast<object>().Concate(...)

Answer 2

If you would have written the full definition of term and hosp instead of var, you would have written something like:

List<AgentTerm_Result> terms = ...
List<AgentHospCashPlan_Result> hosps = ...

Apparently they are not of the same type. You can only concatenate sequences of similar type. Alas you forgot to write us your classes. If a term and a hosp are related, for instance if they have the same super class, you could cast them to the super class before contatenating:

var result = terms.Cast<CommonSuperClass>()
    .Concat(hosps.Cast<CommonSuperClass>();

This also works if they implement the same interface.

If there is nothing common between them, then maybe you should reconsider the idea about concatenating these two lists. After all, what is the use of the collection of Cows mixed with a collection of NewYorkCityBuildings?

Anyway, if you really think there is something common, there are several solutions to concatenate the sequences. Of course you can only use the properties they have in common.

Consider creating an interface that contains the properties that you want to access:

interface IMyInterface
{
     // Properties that are both in AgentTerm_Result and in AgentHospCashPlan
     int Id {get;}
     string Name {get;}
}

class AgentTerm_Result : IMyInterface {...}
class AgentHospCashPlan : IMyInterface{...}

var result = terms.Cast<IMyInterface>()
     .Concat(hosps.Cast<IMyInterface>();

If there are no common properties, consider creating a wrapper class that converts the properties to the proper ones:

class AgentTerm : IMyInterface
{
     public AgentTerm_Result TermResult {get; set;}

     public int Id => this.TermResult.TermResultId;
     public string Name => this.TermResult.TermResultName;

}

and similar for Hosp

IEnumerable<IMyInterface> myTerms = terms.Select(term => new AgentTerm
    {
         TermResult = term,
    })
    .Cast<IMyInterface>();
IEnumerable<IMyInterface> myHosps = hosps.Select(hosp => new MyHosp
    {
        Hosp = hosp,
    })
    .Cast<IMyInterface>();

var result = myTerms.Concat(myHosps);

Answer 3

If you want to return two lists containing two different types, the best option is to return a custom type or even just return a tuple.

To return a custom type, just define up a class to hold the return values:

public class Results
{
    public List<AgentTerm_Result> AgentTerms;
    public List<AgentHospCashPlan_Result> AgentHospCashPlan;
}

Now you can easily do this:

public Results GetResults()
{
    var term = dc.AgentTerm(Agentcode).ToList();
    var hosp = dc.AgentHospCashPlan(Agentcode).ToList();

    return new Results { AgentTerms = term, AgentHospCashPlan = hosp };
}

To use a tuple is slightly easier as you don't need to define anything new:

public (List<AgentTerm_Result>, List<AgentHospCashPlan_Result>) GetResults()
{
    var Agentcode = "";

    var term = dc.AgentTerm(Agentcode).ToList();
    var hosp = dc.AgentHospCashPlan(Agentcode).ToList();

    return (term, hosp);
}   

To get the results of the tuple just call it like this:

var (term, hop) = GetResults();

Then you can use the results as two separate variables.