如何将实体框架从数据库中拉出然后再放入数据表中，以最大程度地减少内存使用 [英] How to minimise memory usage with Entity Framework being pulled from database, then into a DataTable
本文介绍了如何将实体框架从数据库中拉出然后再放入数据表中，以最大程度地减少内存使用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用实体框架提取数据，将其放置在列表中，然后将该列表放置在DataTable中.但是根据查询它要占用大量数据，我不得不删除查询整个数据库的功能，因为它只会使VS崩溃.
I am pulling data using an Entity Framework, placing it in a list, and then placing that list in a DataTable. However it is taking up a lot of data depending on the query, I have had to take out functionality of querying the entire database because it simply crashes VS.
我需要找到一种方法来最小化使用的数据，我希望使用数据表而不是简单的HTML表会有所帮助，但尽管添加了一些不错的功能，但它没有帮助.
I need to find a way to minimize the data used, I was hoping using a data table instead of a simply HTML table would help but it hasn't, although has added some nice functionality.
我觉得需要进行大量更改才能最大程度地减少内存使用.
I have a feeling it will require a large amount of changes in order to minimize memory usage.
这是我的控制人:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication1.Models;
using System.Web.UI.WebControls;
using System.Web.Mvc.Html;
namespace WebApplication1.Controllers
{
public class TableController : Controller
{
[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
public ActionResult Index(System.Web.Mvc.FormCollection collection)
{
DateTime lastMonth = DateTime.Today.AddMonths(-6);
string selectedList = collection["list"];
string selectedGroupType = collection["grouptype"];
IList<SelectListItem> ddl = new List<SelectListItem>();
IList<SelectListItem> ddl2 = new List<SelectListItem>();
var entities = new TableEntities();
var stock = entities.stocks.Take(0).ToList();
if (selectedList == null && selectedGroupType == null)
{
stock = entities.stocks.Take(0).ToList();
}
else if (selectedGroupType == "grouptype=Select+GroupType" || selectedGroupType == null || selectedGroupType == "")
{
if (selectedList == null || selectedList == "")
{
stock = entities.stocks.Take(0).ToList();
}
else
{
stock = entities.stocks.Where(g => (g.ProductGroup == selectedList)).ToList();
}
}
else if (selectedList == "list=Select+Company" || selectedList == null || selectedList == "")
{
stock = entities.stocks.Where(g => (g.GroupType == selectedGroupType)).ToList();
}
else
{
stock = entities.stocks.Where(g => (g.ProductGroup == selectedList) && (g.GroupType == selectedGroupType)).ToList();
}
var stocktemp = entities.stocks.Select(g => g.ProductGroup).Distinct().ToList();
foreach (var item in stocktemp)
ddl.Add(new SelectListItem() { Text = item });
ViewData["list"] = ddl;
stocktemp = entities.stocks.Select(g => g.GroupType).Distinct().ToList();
foreach (var item in stocktemp)
ddl2.Add(new SelectListItem() { Text = item });
ViewData["grouptype"] = ddl2;
stocktemp.Clear();
return View(stock);
//Select(u => { u.StockId, u.ProductGroup , u.Category , u.GroupType , u.ItemType , u.Model , u.SerialNo , u.Status , u.DateArrived , u.CurrentLocation , u.Description , u.TerminalId }).
}
}
}
这是我的视图，它显示了我如何列出数据，尽管我不确定这是否耗尽了内存，因为我认为这是查询本身.
And here is my View which shows how I am listing the data, although I am not sure if this is using up the memory as I believe it is the query itself.
@model IEnumerable<WebApplication1.Models.stock>
<script src="~/scripts/jquery-1.10.2.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" />
<script src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://fonts.googleapis.com/css?family=Roboto:400,500,700,300,100&lang=en">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
<body>
<form class="my-form" method="post" action="~/Table/Index">
<div class="filter">
@Html.DropDownList("list", "Select Company")
@Html.DropDownList("grouptype", "Select GroupType")
<br/>
<button input type="submit"> Submit </button>
</div>
</form>
<div class="scrollingTable">
<h1 id="loading" style="text-align:center;"> LOADING PLEASE WAIT...</h1>
<table class="table-fill" id="myTable">
<thead>
<tr>
<th>
<p1>StockId</p1>
</th>
<th>
<p1>ProductGroup</p1>
</th>
<th>
<p1>Category</p1>
</th>
<th>
<p1>GroupType</p1>
</th>
<th>
<p1>ItemType</p1>
</th>
<th>
<p1>Model</p1>
</th>
<th>
<p1>Serial No</p1>
</th>
<th>
<p1>NR</p1>
</th>
<th>
<p1>Status</p1>
</th>
<th>
<p1>Description</p1>
</th>
<th>
<p1>Date Arrived<</p1>
</th>
<th>
<p1>Current Location</p1>
</th>
<th>
<p1>Terminal ID</p1>
</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.StockId)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProductGroup)
</td>
<td>
@Html.DisplayFor(modelitem => item.Category)
</td>
<td>
@Html.DisplayFor(modelItem => item.GroupType)
</td>
<td>
@Html.DisplayFor(modelItem => item.ItemType)
</td>
<td>
@Html.DisplayFor(modelItem => item.Model)
</td>
<td>
@Html.DisplayFor(modelItem => item.SerialNo)
</td>
<td>
@Html.DisplayFor(modelItem => item.NR)
</td>
<td>
@Html.DisplayFor(modelItem => item.Status)
</td>
<td>
@Html.DisplayFor(modelItem => item.Description)
</td>
<td>
@Html.DisplayFor(modelItem => item.DateArrived)
</td>
<td>
@Html.DisplayFor(modelItem => item.CurrentLocation)
</td>
<td>
@Html.DisplayFor(modelItem => item.TerminalId)
</td>
</tr>
}
</tbody>
</table>
</div>
<script>
$(function () {
$("#myTable").dataTable({ deferRender: true });
$('#loading').hide();
$('#myTable').show();
});
</script>
</body>
推荐答案
出现内存问题的原因之一是，您试图一次将模型中的大量数据加载到视图中.如果是这种情况，那么您需要使用Ajax和虚拟滚动来大块加载数据.有很多方法可以做到这一点，但是由于您提到了DataTables，因此可以在DataTable中使用滚动扩展.
One of the reasons that you are having memory issues is that you are trying to load a huge amounts of data in your model into your view at one. If that is the case, then you need to use Ajax and virtual scrolling to load the data in chunks. There are many ways to do this, but since you mentioned DataTables, you can use the scroller extension in the DataTable.
解决方案
这是完成此操作所需的操作(请注意，此代码将需要进行调整.它无法按原样工作，但是您可以以此为例并以此作为开始方法).
Here is what you need to do to accomplish this (PLEASE NOTE this code will need tweaking. It will not work as is, but you can use this as an example and a way to get started).
下面是一些我编写的C#帮助程序类，用于处理控制器端对jQuery DataTable 的 Ajax 请求，并且还创建了自定义绑定(我已经为 MVC6 编写了此代码，因此您可能需要针对正在使用的MVC的任何版本进行调整):
Below are some C# helper classes that I have written to handle the Ajax request for jQuery DataTable from the controller side and also creates a custom binding (I have written this for MVC6, so you might need to tweak this for whatever version on MVC you are using):
public class DataTableRequest
{
public string sEcho { get; set; }
public int iColumns { get; set; }
public string sColumns { get; set; }
public int iDisplayStart { get; set; }
public int iDisplayLength { get; set; }
public string sSearch { get; set; }
public bool bRegex { get; set; }
public int iSortingCols { get; set; }
public List<DataTableColumnActions> Columns { get; set; }
public List<DataTableSort> Sorts { get; set; }
public IEnumerable<TResult> ToList<T, TResult>(IQueryable<T> Data, Expression<Func<T, TResult>> selector) where T : class where TResult : class
{
if (string.IsNullOrEmpty(sSearch) == false)
{
Data = Data.Filter(sSearch);
}
IQueryable<TResult> result = Data.Select(selector);
if (Sorts.Count > 0 && Columns.Count > 0)
{
foreach (var sort in Sorts)
{
var column = Columns[sort.iSortCol];
result = result.OrderBy(column.mDataProp + " " + sort.sSortDir);
}
}
return result.Skip(iDisplayStart).Take(iDisplayLength).AsEnumerable();
}
public IEnumerable<T> ToList<T>(IQueryable<T> Data) where T : class
{
var result = Data;
if (string.IsNullOrEmpty(sSearch) == false)
{
result = result.Filter(sSearch);
}
if (Sorts.Count > 0 && Columns.Count > 0)
{
foreach (var sort in Sorts)
{
var column = Columns[sort.iSortCol];
result = result.OrderBy(column.mDataProp + " " + sort.sSortDir);
}
}
return result.Skip(iDisplayStart).Take(iDisplayLength).AsEnumerable();
}
public object ToDataTableResult<T, TResult>(IQueryable<T> Data, Expression<Func<T, TResult>> selector) where T : class where TResult : class
{
IQueryable<T> inData = Data;
if (string.IsNullOrEmpty(sSearch) == false)
{
inData = inData.Filter(sSearch);
}
IQueryable<TResult> result = inData.Select(selector);
if (Sorts.Count > 0 && Columns.Count > 0)
{
foreach (var sort in Sorts)
{
var column = Columns[sort.iSortCol];
result = result.OrderBy(column.mDataProp + " " + sort.sSortDir);
}
}
return new
{
draw = sEcho,
recordsTotal = Data.Count(),
recordsFiltered = result.Count(),
data = result.Skip(iDisplayStart).Take(iDisplayLength).AsEnumerable()
};
}
public object ToDataTableResult<T>(IQueryable<T> Data) where T : class
{
var result = Data;
if (string.IsNullOrEmpty(sSearch) == false)
{
result = result.Filter(sSearch);
}
if (Sorts.Count > 0 && Columns.Count > 0)
{
foreach (var sort in Sorts)
{
var column = Columns[sort.iSortCol];
result = result.OrderBy(column.mDataProp + " " + sort.sSortDir);
}
}
return new
{
draw = sEcho,
recordsTotal = Data.Count(),
recordsFiltered = result.Count(),
data = result.Skip(iDisplayStart).Take(iDisplayLength).AsEnumerable()
};
}
}
public class DataTableColumnActions
{
public string mDataProp { get; set; }
public string sSearch { get; set; }
public bool bRegex { get; set; }
public bool bSearchable { get; set; }
public bool bSortable { get; set; }
}
public class DataTableSort
{
public int iSortCol { get; set; }
public string sSortDir { get; set; }
}
public class DataTableRequestProvider : IModelBinderProvider
{
public IModelBinder GetBinder(ModelBinderProviderContext context)
{
if (context == null) throw new ArgumentNullException(nameof(context));
if (context.Metadata.ModelType == typeof(DataTableRequest))
return new DataTableRequestBinder();
return null;
}
public class DataTableRequestBinder : Attribute, IModelBinder
{
public async Task BindModelAsync(ModelBindingContext bindingContext)
{
await Task.Run(() =>
{
int index;
var request = new DataTableRequest();
request.bRegex = Convert.ToBoolean(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "bRegex").Select(i => i.Value.ToString()).FirstOrDefault());
request.sSearch = bindingContext.HttpContext.Request.Query.Where(i => i.Key == "sSearch").Select(i => i.Value.ToString()).FirstOrDefault();
request.sEcho = bindingContext.HttpContext.Request.Query.Where(i => i.Key == "sEcho").Select(i => i.Value.ToString()).FirstOrDefault();
request.sColumns = bindingContext.HttpContext.Request.Query.Where(i => i.Key == "sColumns").Select(i => i.Value.ToString()).FirstOrDefault();
request.iColumns = Convert.ToInt32(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "iColumns").Select(i => i.Value.ToString()).FirstOrDefault());
request.iDisplayLength = Convert.ToInt32(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "iDisplayLength").Select(i => i.Value.ToString()).FirstOrDefault());
request.iDisplayStart = Convert.ToInt32(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "iDisplayStart").Select(i => i.Value.ToString()).FirstOrDefault());
request.iSortingCols = Convert.ToInt32(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "iSortingCols").Select(i => i.Value.ToString()).FirstOrDefault());
request.Sorts = new List<DataTableSort>();
request.Columns = new List<DataTableColumnActions>();
index = 0;
while (bindingContext.HttpContext.Request.Query.Keys.Any(k => k == "iSortCol_" + index))
{
var sort = new DataTableSort();
sort.iSortCol = Convert.ToInt32(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "iSortCol_" + index).Select(i => i.Value.ToString()).FirstOrDefault());
sort.sSortDir = bindingContext.HttpContext.Request.Query.Where(i => i.Key == "sSortDir_" + index).Select(i => i.Value.ToString()).FirstOrDefault();
request.Sorts.Add(sort);
index++;
}
index = 0;
while (bindingContext.HttpContext.Request.Query.Keys.Any(k => k == "mDataProp_" + index))
{
var column = new DataTableColumnActions();
column.bRegex = Convert.ToBoolean(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "bRegex_" + index).Select(i => i.Value.ToString()).FirstOrDefault());
column.bSearchable = Convert.ToBoolean(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "bSearchable_" + index).Select(i => i.Value.ToString()).FirstOrDefault());
column.bSortable = Convert.ToBoolean(bindingContext.HttpContext.Request.Query.Where(i => i.Key == "bSortable_" + index).Select(i => i.Value.ToString()).FirstOrDefault());
column.mDataProp = bindingContext.HttpContext.Request.Query.Where(i => i.Key == "mDataProp_" + index).Select(i => i.Value.ToString()).FirstOrDefault();
column.sSearch = bindingContext.HttpContext.Request.Query.Where(i => i.Key == "sSearch_" + index).Select(i => i.Value.ToString()).FirstOrDefault();
request.Columns.Add(column);
index++;
}
bindingContext.Result = ModelBindingResult.Success(request);
return Task.CompletedTask;
});
}
}
}
public static class IQueryableExtension
{
public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source, string orderByValues) where TEntity : class
{
IQueryable<TEntity> returnValue = null;
string orderPair = orderByValues.Trim().Split(',')[0];
string command = orderPair.ToUpper().Contains("DESC") ? "OrderByDescending" : "OrderBy";
var type = typeof(TEntity);
var parameter = Expression.Parameter(type, "p");
string propertyName = (orderPair.Split(' ')[0]).Trim();
System.Reflection.PropertyInfo property;
MemberExpression propertyAccess;
if (propertyName.Contains('.'))
{
// support to be sorted on child fields.
String[] childProperties = propertyName.Split('.');
property = typeof(TEntity).GetProperty(childProperties[0]);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
for (int i = 1; i < childProperties.Length; i++)
{
Type t = property.PropertyType;
if (!t.IsConstructedGenericType)
{
property = t.GetProperty(childProperties[i]);
}
else
{
property = t.GetGenericArguments().First().GetProperty(childProperties[i]);
}
propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
}
}
else
{
property = type.GetProperty(propertyName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
}
var orderByExpression = Expression.Lambda(propertyAccess, parameter);
var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },
source.Expression, Expression.Quote(orderByExpression));
returnValue = source.Provider.CreateQuery<TEntity>(resultExpression);
if (orderByValues.Trim().Split(',').Count() > 1)
{
// remove first item
string newSearchForWords = orderByValues.ToString().Remove(0, orderByValues.ToString().IndexOf(',') + 1);
return source.OrderBy(newSearchForWords);
}
return returnValue;
}
public static IQueryable<T> Filter<T>(this IQueryable<T> query, string search)
{
var properties = typeof(T).GetProperties().Where(p => p.PropertyType == typeof(String));
var predicate = PredicateBuilder.New<T>(false);
foreach (PropertyInfo property in properties)
{
predicate = predicate.Or(CreateLike<T>(property, search));
}
return query.AsExpandable().Where(predicate);
}
private static Expression<Func<T, bool>> CreateLike<T>(PropertyInfo prop, string value)
{
var parameter = Expression.Parameter(typeof(T), "f");
var propertyAccess = Expression.MakeMemberAccess(parameter, prop);
// make sure string is not null
var notNull = Expression.NotEqual(propertyAccess, Expression.Constant(null, typeof(string)));
// convert to lower case
var toLower = Expression.Call(propertyAccess, typeof(string).GetMethod("ToLower", System.Type.EmptyTypes));
// comparison on lower case
var like = Expression.Call(toLower, "Contains", null, Expression.Constant(value.ToLower(), typeof(string)));
return Expression.Lambda<Func<T, bool>>(Expression.AndAlso(notNull, like), parameter);
}
}
您需要添加以下 global.asax
ModelBinderProviders.BinderProviders.Add(new DataTableRequestBinder());
如果您使用的是MVC6，请在 ConfigureServices
If you are using MVC6, then add the following instead to Startup.cs under ConfigureServices
services.AddMvc()
.AddJsonOptions(options =>
{
// handle loops correctly
options.SerializerSettings.ReferenceLoopHandling =
Newtonsoft.Json.ReferenceLoopHandling.Ignore;
// use standard name conversion of properties
options.SerializerSettings.ContractResolver =
new CamelCasePropertyNamesContractResolver();
// include $id property in the output
options.SerializerSettings.PreserveReferencesHandling =
PreserveReferencesHandling.Objects;
}).AddMvcOptions(options =>
{
options.ModelBinderProviders.Insert(0, new DataTableRequestProvider());
});
更改您的索引操作，以仅生成 ViewData ，并返回一个空的视图(为简单起见，我省略了ViewData部分)
Change your Index action to only build the ViewData and return an empty view (I omitted the ViewData part for simplicity)
public ActionResult Index()
{
return View();
}
您需要一个使用EF读取数据的操作(我称之为 Read ):
You need an action that reads the data using EF (I called it Read):
public JsonResult Read([DataTableRequestBinder]DataTableRequest request)
{
var data = entities.stocks; // you can add your Where() and transformation here as well
return Json(request.ToDataTableResult(data));
}
然后您的视图应如下所示:
<script src="~/scripts/jquery-1.10.2.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" />
<script src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/scroller/1.4.2/js/dataTables.scroller.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://fonts.googleapis.com/css?family=Roboto:400,500,700,300,100&lang=en">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
<body>
<form class="my-form" method="post" action="~/Table/Index">
<div class="filter">
@Html.DropDownList("list", "Select Company")
@Html.DropDownList("grouptype", "Select GroupType")
<br/>
<button input type="submit"> Submit </button>
</div>
</form>
<div class="scrollingTable">
<h1 id="loading" style="text-align:center;"> LOADING PLEASE WAIT...</h1>
<table class="table-fill" id="myTable">
<thead>
<tr>
<th>
<p1>StockId</p1>
</th>
<th>
<p1>ProductGroup</p1>
</th>
<th>
<p1>Category</p1>
</th>
<th>
<p1>GroupType</p1>
</th>
<th>
<p1>ItemType</p1>
</th>
<th>
<p1>Model</p1>
</th>
<th>
<p1>Serial No</p1>
</th>
<th>
<p1>NR</p1>
</th>
<th>
<p1>Status</p1>
</th>
<th>
<p1>Description</p1>
</th>
<th>
<p1>Date Arrived</p1>
</th>
<th>
<p1>Current Location</p1>
</th>
<th>
<p1>Terminal ID</p1>
</th>
</tr>
</thead>
</table>
</div>
<script>
$(function () {
$("#myTable").DataTable({
"processing": true,
"serverSide": true,
"deferRender": true,
"scrollY": 200,
"scrollCollapse": true,
"scroller": true,
"ajaxSource": "@Url.Action("Read", "Table")",
"columns": [
{ "data": "StockId" },
{ "data": "ProductGroup" },
{ "data": "Category" },
{ "data": "GroupType" },
{ "data": "ItemType" },
{ "data": "Model" },
{ "data": "SerialNo" },
{ "data": "NR" },
{ "data": "Status" },
{ "data": "Description" },
{ "data": "DateArrived" },
{ "data": "CurrentLocation" },
{ "data": "TerminalID" },
]
});
});
</script>
所有Json的工作都需要 Newtonsoft.Json .
You will need Newtonsoft.Json for the all Json work.
下面是一些参考资料，您可以看一下:
Below are some references that you can take a look at:
- DataTable Ajax
- DataTable Scroller
这篇关于如何将实体框架从数据库中拉出然后再放入数据表中，以最大程度地减少内存使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
