Get data ALL data from 2 tables using linqtosql VB.NET

Question

0.00/5 (No votes)

See more:

Hi,

I want to get all data from 2 table using linqtosql.
The below works and gives the correct results (would like to sort it yet too) but its incredibly slow.

I simply want all the records from 2 tables.

Could someone help with this?

What I have tried:

qCustomerVendorNames = (
                           From tmpCust In m_dbCustomersVendors.Customers
                           Select New With {
                               .Name = tmpCust.CustomerName,
                               .CompanyType = "Customer",
                               tmpCust.UniqueID
                           }
                       ).Union(
                           From tmpVend In m_dbCustomersVendors.Vendors
                           Select New With {
                               .Name = tmpVend.VendorName,
                               .CompanyType = "Vendor",
                               tmpVend.UniqueID
                           }
                       )

Posted 26-Nov-19 3:13am

ben the great

Updated 26-Nov-19 5:36am

Dave Kreskowiak

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2019-11-26T05:36:00

Solution 1

It's slow because you're doing a projection, the Select New. You're creating a new object for every record in both tables and then performing a Union on them.

If you've got thousands of records in these tables, you're creating thousands of objects, one at a time. That's going to take a while.

If you want to speed this up, you're going to have to either avoid the projection or have the database do it everything for you, but that means you're going to have to avoid using LINQ and go direct to the database.

Posted 26-Nov-19 5:36am

Dave Kreskowiak

Comments

ben the great 26-Nov-19 13:04pm

Thank you for replying.

Can you give me an example of how you would handle this? I am using this to set the datasource on a devexpress xtragrid incase that matters.

Dave Kreskowiak 26-Nov-19 16:01pm

Create a View in the database that holds all of the records and the fields required.
Setup the view in your context just like you would for a normal database table.
After that, it's just a simple From...Select on that "table".

ben the great 26-Nov-19 16:42pm

how do i create it without any relationships just select all records from both tables?
with column names like in my code. so customer name and vendor name in same column. first all the customers then all the vendors.

ben the great 26-Nov-19 17:21pm

SELECT dbo.Customer.CustomerName, dbo.Customer.UniqueID FROM dbo.Customer UNION SELECT dbo.Vendor.VendorName, dbo.Vendor.UniqueID
FROM dbo.Vendor

This seems to work in a view created in sql management studio. then i created a datacontext and dragged the view onto it.

then i did this in my code:

qCustomerVendorNames = From tmp In m_dbViewCustVend.View_CustomersVendors
Select New With {.Name = tmp.CustomerName, tmp.UniqueID}

It seems to load much faster. Any reason not to keep going like that?

Dave Kreskowiak 26-Nov-19 17:28pm

That's it. You're still doing a projection, which creates an object for every record returned, but that's up to you. Most of the heavy work is being done by the database now instead of on the client.

Maciej Los 27-Nov-19 4:05am

5ed!

Maciej Los 27-Nov-19 4:05am

5ed!