Query multiple tables c# linq efficiently

Question

I have a database with about 20 table, all of them have some columns which are same, eg, name, cost, year manufactured etc. I need to query those tables. what is best, most efficient way. tables and data have to stay as they are.

Here is what I am doing right now.

var table1 = (from a in _entities.ta1
              join b in _entities.subTa on a.tid equals b.Id
              select new
                     {
                         id = a.id,
                         name = a.name,
                         type = a.type,
                     }).ToList();
var table2 = (from a in _entities.ta2
              join b in _entities.subTa2 on a.tid equals b.Id
              select new
                     {
                         id = a.id,
                         name = a.name,
                         type = a.type,
                     }).ToList();

var abc = table1;
abc.AddRange(table2)

var list = new List<MyClass>();

foreach(var item in abc)
{
      var classItem = new MyClass();
      classItem.id = item.id;
      classItem.name = item.name;
      classItem.type = item.type;

      list.Add(classItem);
}

return list;

This needs to be done to many table, which is not very efficient coding.

How can I improve this code?

I have a database with about 20 table, all of them have some columns which are same, eg, name, cost, year manufactured ... don't you think that it means that database is badly designed ? — Selvin
– Selvin, Commented May 14, 2021 at 11:46
All you need is to define them with attributes, no? What is the point of having same structure on 20 tables? In Linq you seldom need a join, that should be resolved by navigation properties if you have good relations < g > in the database. Probably your Linq queries are also wrong as your database design. Would you care to share some real sample. — Cetin Basoz
– Cetin Basoz, Commented May 14, 2021 at 11:49
@TimSchmelter Why ? what is wrong with var table1= ...code from question but instead anonymouse class use MyClass ....ToList(); table.AddRange(...code from second query in the question without ToList but with MyClass); return table1; over using union — Selvin
– Selvin, Commented May 14, 2021 at 11:50
It is, but cannot change it at the moment. so have to come up with some efficient way. — toadgs
– toadgs, Commented May 14, 2021 at 11:52
Make all classes implement an interface an map the interface to MyClass using AutoMapper. — Gert Arnold
– Gert Arnold, Commented May 14, 2021 at 12:09

Aly Elhaddad · Accepted Answer · 2021-05-14 11:57:31Z

You could use that, assuming that data types for all columns do match.

List<MyClass> list =
    ( //table1
     from a in _entities.ta1
     join b in _entities.subTa
     on a.tid equals b.Id
     select new MyClass()
     {
         id = a.id,
         name = a.name,
         type = a.type
     })
    .Concat(( //table2 (use .Union instead of .Concat if you wish to eliminate duplicate rows)
     from a in _entities.ta2
     join b in _entities.subTa2
     on a.tid equals b.Id
     select new MyClass()
     {
         id = a.id,
         name = a.name,
         type = a.type
     }
    )).ToList();
return list;

The reason why this could be more efficient is that it

groups all queries into one database query, thus causes less database traffic,
lets all the computation happen on the database server, therefore eliminating duplicate computation in the OP code, and
optimizes memory usage as it does NOT create a list of anonymous objects and then adding them to a new List<MyClass>.

toadgs · Accepted Answer · 2021-05-20 07:40:12Z

0

This is the answer i was looking or. this is not ideal but it got the job done

 var tables= new Dictionary<string, string>();
 tables.Add("table1", "subTable1");
 tables.Add("table2", "subTable2");
 foreach (var table in tables)
            {
                var tableName = table.Key;
                var subName= table.Value;
                var data = _entities.Database.SqlQuery<MyClass>($@"select 
                a.Id,a.Name,b.subName from {tableName} a  left join {subName} b on 
                a.subId=b.Id").ToList();
            }

Thank you everyone for your contribution

answered May 20, 2021 at 7:40

toadgs

435 bronze badges

Collectives™ on Stack Overflow

Query multiple tables c# linq efficiently

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related