How to return more than one database Entity in a LIST?

Question

I have two entities, CATEGORY and TRANSACTION, on the bench I also have these two tables.

TABELA: CATEGORIA
CHAVE: ID

TABELA: TRANSACAO
CHAVE: CATEGORIA_ID

When performed a select for Entity performing a join:

var _result = (from a in contenxt.TRANSACAO
                          join b in contenxt.CATEGORiAs on a.CATEGORIA_ID equals b.
                          select new
                          {
                             a.ID,
                             b.TIPO,
                             b.ID
                           });

How do I store this in a LIST?

I would like to do the data manipulation and do not know how to do, because usually I return a LIST of a specific entity, but I don’t know how to do when they mix.

Answer 1

In fact it is a problem. As I have never used anything so I do not know if you have a better solution than the below.

You can create a class to receive this new type created by this query, then you would return List<Resultado> if this class calls Resultado.

This is because the way it is is creating an anonymous type to receive the result, so you don’t know what that type is until the execution.

It doesn’t have to be a class anyway. You can create a tuple with the composition of this type, there could use something like List<Tuple<tipoID, tipoTIPO, tipoID>>. C# 7 has tuple in the language and its use is usually more interesting.

You can explicitly declare this as a list and access fields as list elements. It only works if all fields are of the same type. Of course you can use one List<object> to accept any type but loses the type guarantee.

Finally you can return to consider that the return of your function is List<object>, after all the anonymous type created is certainly a object. Loses strong typing but is a solution. It can still return a List<dynamic> but I doubt it’s better to return object.

I will use the obtained example on that page to show what happens with the result and what types we are talking about:

using System.Console;
using System.Linq;
using System.Collections.Generic;

class Customer {
    public int ID { get; set; }
    public string Name { get; set; }
}

class Order {
    public int ID { get; set; }
    public string Product { get; set; }
}

public static class Program {
    public static void Main() {
        // Example customers.
        var customers = new Customer[] {
            new Customer{ID = 5, Name = "Sam"},
            new Customer{ID = 6, Name = "Dave"},
            new Customer{ID = 7, Name = "Julia"},
            new Customer{ID = 8, Name = "Sue"}
        };
    
        // Example orders.
        var orders = new Order[] {
            new Order{ID = 5, Product = "Book"},
            new Order{ID = 6, Product = "Game"},
            new Order{ID = 7, Product = "Computer"},
            new Order{ID = 8, Product = "Shirt"}
        };
    
        // Join on the ID properties.
        var query = from c in customers
                join o in orders on c.ID equals o.ID
                select new { c.Name, o.Product };

        WriteLine(query.GetType());

        // Display joined groups.
        foreach (var group in query) {
            WriteLine("{0} bought {1}", group.Name, group.Product);
        }
        var lista = query.ToList();
        WriteLine(lista.GetType());
        
        //criando explicitamente uma lista de string (neste caso é possível)
        var query2 = from c in customers
                join o in orders on c.ID equals o.ID
                select new List<string> { c.Name, o.Product };

        WriteLine(query2.GetType());


        foreach (var group in query2) {
            WriteLine("{0} bought {1}", group[0], group[1]);
        }
        var lista2 = query2.ToList();
        WriteLine(lista2.GetType());
    }
}

Behold working in the ideone. And in the .NET Fiddle. Also put on the Github for future reference.

Example using an explicit class. Ali conforms to what I said.

Rebound that there may be better solution but my lack of experience prevents me from stating. If I remember something I update here.

Answer 2

I believe your problem is to return this List from a method.

You are doing a query, whose type is a anonymous type. You cannot create a method that returns a list of an anonymous type.

I suggest you create a class with this 3 information that you want to return. Then you can do something like this:

List<CategoriaTransacao> tuaLista = (from a in contenxt.TRANSACAO
                      join b in contenxt.CATEGORiAs on a.CATEGORIA_ID equals b.
                      select new CategoriaTransacao
                      {
                         TransacaoId = a.ID,
                         CategoriaTipo = b.TIPO,
                         CategoriaId = b.ID
                       }).ToList();

In the example above I have a class CategoriaTransacao which has 3 properties, equivalent to what you want to return.

So you can return in a method.