How to get data from two tables in the same column?

Asked

Viewed 619 times

1

I have two tables in the database:

Tabela1
Id      Nome       Sexo
1       NomeA      Macho
2       NomeC      Fêmea

Tabela2
Id      Nome       Sexo
1       NomeB      Macho
2       NomeD      Fêmea

I would like to have the following result:

Id      Nome       Sexo       Tabela
1       NomeA      Macho      Tabela1
2       NomeB      Fêmea      Tabela2
1       NomeC      Macho      Tabela1
2       NomeD      Fêmea      Tabela2

Note that it is in alphabetical order of the two tables.

How could I make a SELECT in the SQL Server to return a result like this?

Using Linq with Entity Framework, is it possible to do the same? (In this case we have db.Table 1 and db.Table 2)

c# sql sql-server entity-framework linq

2 answers

4


If the columns are the same, you can use a SELECT with UNION (to avoid duplication) or UNION ALL if you want to display the result even with duplicates:

SELECT ID, NOME, SEXO, 'Tabela1' as TABELA
FROM TABELA1
ORDER BY NOME

UNION 

SELECT ID, NOME, SEXO, 'Tabela2' as TABELA
FROM TABELA2
ORDER BY NOME

In the Entity Framework:

var uniao = db.Tabela1.Select(new {
    Id = Id,
    Nome = Nome,
    Sexo = Sexo,
    Tabela = 'Tabela1'
}).ToList().Union(db.Tabela2.Select(new {
    Id = Id,
    Nome = Nome,
    Sexo = Sexo,
    Tabela = 'Tabela2'
}).ToList()).OrderBy(x => x.Nome);

  • Thank you @Gypsy, but how would I put in alphabetical order taking into account the two tables?

  • At the end of query you need to put a order by. Ex.: ... .ToList()).OrderBy(x => x.Nome);

  • @Gypsy, it is necessary to use the ToList() twice? Wouldn’t it be better to put only once at the end?

  • 2

    @Jéfersonbueno I use to enumerate the result in memory and not depend on the bank when joining the collections. In theory the performance is slightly higher.

  • @Oh gypsy omorrisonmendez, I get it. Thanks for the answer.

  • 1

    @Jedaiasrodrigues I updated the answer.

Show 1 more comment

0

One option you would have is to create a View in SQL Server:

CREATE VIEW vw_Tabelas AS
  SELECT
      [Id]
    , [Nome]
    , [Sexo]
    , 'Tabela1' AS [Tabela]
  FROM
    Tabela1

  UNION

  SELECT
      [Id]
    , [Nome]
    , [Sexo]
    , 'Tabela2'
  FROM
    Tabela2

So your query ends up being just like this:

SELECT [Id], [Nome], [Sexo], [Tabela] FROM vw_Tabelas ORDER BY [Nome]

And the same LINQ:

var resultados = db.vw_Tabelas.Select().ToList().OrderBy(x => x.Nome);

Browser other questions tagged c# sql sql-server entity-framework linq

You are not signed in. Login or sign up in order to post.