0

I’m working on a project with over 15 databases, I need always to join tables from different entities so I end up using .ToList.

I had an advice from a friend to do a database link-server and then to create views in the same database for all the references tables.

But I'm not happy with both of them.

Is there any alternative solution other than .ToList & database views and what is the best practice in this case?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Basil
  • 1,613
  • 12
  • 25
  • Do you need the result of the Query entirely in memory or not ? Than ToList is the right thing, there's nothing bad about this. You can iterate item by item with Foreach as Alternative - if you don't need all at the same time. "not happy" does not really explain your preferences. – Holger Dec 27 '19 at 11:34
  • ToList will fetch the entire query which I don’t want. For example if you want to join two tables and give one of them .tolist it fetch all records then it filter with join which is not correct that’s why I’m asking – Basil Dec 27 '19 at 11:38
  • If you don't want to fetch them, don't use ToList !. It's the last thing you do in a query. You must want to fetch something, otherwise you would not need a query ! If you fetch too much, you append where-conditions and say which one you want ... If you query something that you don't want to query, your query is wrong, this has nothing to do with ToList(). ToList means something like "Execute query now". – Holger Dec 27 '19 at 11:44
  • Ok!! Did you read my question.I’m asking for a solution when I have two deferent entities – Basil Dec 27 '19 at 11:47
  • Yes, but there is no Code in your question, and "having entities" and fetching is the same thing. The entities in memory are established when fetching. You should have the SQL-server doing the cross-database joins. Do you run different Queries, on different DbContext and try to Join them in memory ? You don't need ToList for that (just omit it), but it will load both tables from both servers. How could you do a join without loading the two source tables ? That's impossible by definition. You can accellerate it with ToDictionary maybe, or query one table with a grouped query. – Holger Dec 27 '19 at 11:57
  • I know just trying to see if someone has a better idea – Basil Dec 27 '19 at 12:04
  • General solutions are always 'bad'. You can always improve by knowing your data better than the server. If you know you fetch only a few hundred records from one table, you load the smaller table first (and in a dictionary), and only load the matching records from the other table, by passing the ID's as input in the second query. For each incoming Item you search in your local dictionary for the corresponding item and form your join-output. You can also sort both tables by the join-id, this saves the dictionary, but it's little more coding in finding the match. – Holger Dec 27 '19 at 12:16
  • I’m doing exactly what you are saying from 5 years ago, I want to see if in this 5 years they came with new solution like the mssql link server, they can have entities link or something :( – Basil Dec 27 '19 at 12:19
  • And you summarized what you are doing with "I'm using ToList ?" You can not ask for "better", if you don't say "better then what". And the link server you cancelled already by saying "you are not happy with it", that means you don't want it. Yes, use a Link-Server but this has nothing to do with EF. For EF the Link-Server is just a new SQL-Server. That means you also have to join your contexts to profit from that. Or you use it contextless if readonly is sufficient. – Holger Dec 27 '19 at 12:31

1 Answers1

1

Plenty of ways around this, each with their own disadvantages.

Linked databases with views

Check the usage on the Microsoft docs

The ability to access data from outside of SQL Server.

The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

The ability to address diverse data sources similarly.

Point three is your case exactly. You also have the ability to link multiple databases like mysql if need be.

A lot of disadvantages though (check here). I will add one of my own and say that

Implement with code and automapper

If all the tables are similar, then you can use a tool like automapper to make easy lists from your data

  1. Get your data with entity framework
  2. Map to DTO objects with the common properties using automapper
  3. Merge your lists with Range add.

Duplicate data

We live in a world were nosql solutions are used alongside RDBMS solutions. it might be that you create a common db (RDBMS or nosql or whatevers suits you), and duplicate your data there.

It's extra work but it's the fastest in usage.

I could think of more, but this is the gist of it.

Athanasios Kataras
  • 25,191
  • 4
  • 32
  • 61
  • Our company has a legacy systems and the database structure is very bad i came in a late stage, so i have to live with it.the tables are very huge so I’m looking for a solution can run without getting all table objets in the server ram – Basil Dec 27 '19 at 07:56
  • 1
    Since you want to use `ToList()` you can't avoid that. The coded option says that you need to execute the individual queries, automap and then merge. It might be a real waste of time. Beware though that linked servers are notoriously slow in queries and that deadlocks in one DB, might propagate problems to another. – Athanasios Kataras Dec 27 '19 at 08:00
  • Honestly I’m using tolist because it’s the easiest way to achieve what ever I want now because it’s the best practice – Basil Dec 27 '19 at 15:53