Entity framework and a lot of data

Question

0.00/5 (No votes)

See more:

I'm using database first approach with SQLite database. Data is spread across multiple tables. While fetching data for a single item, i need to read the data from all the tables and get the corresponding data. I'm facing issues while querying huge amount of data.

What I have tried:

I have 3 tables Transactions, Records and Fields.

Transactions Table is the main table that contains transactions with its primary key TransactionId (GUID). This same TransactionId is used in Records table as ForeignKey.

The Record table has RecordId (GUID) as its PrimaryKey that is referenced by Fields table.

I need to get a single transaction with all its corresponding records and its fields from DB based on a GUID which is passed into the function.

This is the code i'm trying to execute

C#

public Transaction LoadTransaction(Guid transactionId)
        {
                context.Fields.Load();
                context.Records.Load();
                var transaction = context.Transactions.ToList().Where(p => p.TransactionId == transactionId).OrderBy(o => o.CreationDate).FirstOrDefault();

                return transaction;
        }

This works fine with less number of rows.But when tested with 20K rows it fails and throws the OutOfMemory exception when it reaches

C#

context.Fields.Load();

Fields Table contains all the data and it contains Image data too that makes it large.

This is the generated SQL query when i get while hovering over the statement.

SQL

{SELECT 
[Extent1].[FieldId] AS [FieldId], 
[Extent1].[FieldNumber] AS [FieldNumber], 
[Extent1].[FieldValue] AS [FieldValue], 
[Extent1].[RecordId] AS [RecordId], 
[Extent1].[TransactionId] AS [TransactionId]
FROM [Field] AS [Extent1]}

Posted 8-Jan-17 18:30pm

Sanjay Dev

Updated 9-Jan-17 0:25am

v2

Add a Solution

Comments

Wendelius 9-Jan-17 1:52am

Few questions:
- how many rows you're trying to fetch?
- what does the generated SQL look like?

Sanjay Dev 9-Jan-17 4:23am

I'm trying to fetch about 200 rows based on a condition from a total list of 20000+.

The generated SQL looks like a usual Select statement with all the column names. Before querying the data from the main table i had loaded the other 2 tables using .Load which containes related Data.

Its like there are 3 tables. MainTable , DetailsTable and SubDetailsTable(details associated with DetailsTable).

I used to load DetailsTable and SubDetailsTable usind .Load()

Then query the MainTable. So i get associated data from other two tables while i fetch a single item from MainTable.

OutOfMemory exception comes right when it comes to context.SubDetailsTable.Load()

SubDetailsTable contains 20K rows.

Wendelius 9-Jan-17 4:28am

In general 20'000 rows in a table is small and you're fetching only 200 rows so everything should be fine. What I'm thinking of is that are the joins correctly in place so that you don't have Cartesian product. Can you post the generated sql?

Sunasara Imdadhusen 9-Jan-17 5:06am

Provide the exact query that you are running on the database

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)

Wendelius · Answer 1 · 2017-01-09T00:25:00

It looks like you fetch all the rows from fields and records separately and you don't join them to transaction.

What if you try a bit different syntax

C#

var query = from t in context.Transactions
            join f in context.Fields on t.TransactionId equals f.TransactionId
            join r in context.Records in f.RecordId equals r.RecordId
            where t.TransactionId == transactionId
            select transaction;

However, that would return data only form transaction so if you need data from the other tables also you need to modify the select portion accordingly.

[ADDITION]

C#

var query = from t in context.Transactions
            join r in context.Records in t.TransactionId equals r.TransactionId
            join f in context.Fields on r.RecordId equals f.RecordId
            where t.TransactionId == transactionId
            select transaction;