CODEWITHSUNDEEP
vb.netlinqentity-framework
NotMe
NotMe

Reputation: 805

Using LINQ to select all from multiple tables

I have a very weird question to ask.

I have 6 tables that I need to get data from:

TEMPLATE
TEMPLATE_INDEX
TABLE_1
TABLE_2
TABLE_3
TABLE_4

The template has a FK to another table (INTEREST), which is used as the key to get the data. It is a one-to-many relationship, 1 INTEREST has many TEMPLATE

How the tables are linked

TEMPLATE <----- TEMPLATE_INDEX
TEMPLATE_INDEX <----- TABLE_1
TEMPLATE_INDEX <----- TABLE_2
TEMPLATE_INDEX <----- TABLE_3
TEMPLATE_INDEX <----- TABLE_4

I'm trying to download all the data pertaining to the INTEREST selected upon loading of the UserControl (I don't know if this is a good idea but my thinking is that, there will be less querying of the database when the data is needed).

I have tried doing this in VB.NET

dim temp_listing = From q In context.TEMPLATE _
                   Join t In context.TEMPLATE_INDEX On t.TEMPLATE_ID Equals q.ID
                   Join t1 In context.TABLE_1 On t1.TEMPLATE_INDEX_ID Equals t.ID
                   Join t2 In context.TABLE_2 On t2.TEMPLATE_INDEX_ID Equals t.ID
                   Join t3 In context.TABLE_3 On t3.TEMPLATE_INDEX_ID Equals t.ID
                   Join t4 In context.TABLE_4 On t4.TEMPLATE_INDEX_ID Equals t.ID
                   Select q, t, t1, t2, t3, t4

temp_listing.Load()

But not all the data gets downloaded.

Can anyone enlighten me on how to do this?

The reason why I'm doing this is because the database is not local. A lot of times, it takes a while for the relevant information to appear after the user has done something. I thought that it would be easy to have a longer loading time and make the program run smoothly after, since all data will be local (i.e. context.TABLE_1.Local)

If anyone, has a better solution, I'm all ears.

Thanks.

Upvotes: 3

Views: 1580

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109079

First thing to note is that you should avoid the Join statement in LINQ statements to a SQL backend. It is verbose and nearly always you can (and should) do the same thing with navigation properties.

I suspect that the problem here is the INNER JOIN. Contrary to OUTER JOIN it only returns data where both sides of the join are present.

In stead of this, use the Include statement with navigation properties:

From q In context.Template
                 .Include(t => t.TEMPLATE_INDEX.TABLE_1)
                 .Include(t => t.TEMPLATE_INDEX.TABLE_2)
                 .Include(t => t.TEMPLATE_INDEX.TABLE_3)
                 .Include(t => t.TEMPLATE_INDEX.TABLE_4)
Select q

This will translate to outer joins.

A second remark is that generally it is a good idea to get data in one roundtrip. But you'll have to do some benchmarking here. A query with "many" joins has a way of blowing up the result set in length and in width. In the end, you may send an excessive amount of data over the wire, taking much longer than a few separate roundtrips sending less data.

Upvotes: 2

Related Questions