实体框架和大量数据 [英] Entity framework and a lot of data

问题描述

我正在使用SQLite数据库的数据库第一种方法。数据分布在多个表中。在获取单个项目的数据时，我需要从所有表中读取数据并获取相应的数据。我在查询大量数据时遇到问题。



我尝试过：



我有3个表交易，记录和字段。



事务表是包含主键TransactionId（GUID）的事务的主表。这个TransactionId在Records表中用作ForeignKey。



Record表有RecordId（GUID）作为其FieldsKey引用的PrimaryKey。



我需要根据传递给函数的GUID从DB中获取所有相应记录及其字段的单个事务。





这是我正在尝试执行的代码

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

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;
        }

这样可以正常工作，行数较少。但是当用20K行测试时，它会失败并抛出OutOfMemory异常当它到达

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

context.Fields.Load();

字段表包含所有数据，它也包含图像数据，使其变大。



这是当我将鼠标悬停在语句上时生成的SQL查询。 />

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.

{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]}

推荐答案

看起来你分别从字段和记录中获取所有行你没有加入他们交易。



如果你尝试不同的语法怎么办？

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

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;

但是，这只会从transa返回数据因此，如果您需要其他表中的数据，您还需要相应地修改选择部分。



[附加]

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]

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;

这篇关于实体框架和大量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！