在LINQ to Entity Dynamic Select中显示自定义字段

本文介绍了在LINQ to Entity Dynamic Select中显示自定义字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设我有一个具有四个属性的实体Container:ContainerID(整数)，ContainerNumber(字符串)，Title(字符串)和Date(日期).

Let's say I have an entity Container with four properties: ContainerID (Integer), ContainerNumber (String), Title (String), and Date (Date).

我有一个名为ContainerCustomFields的第二个实体，它具有三个属性:CustomFieldID(整数)，ContainerID(整数和外键)，FieldName(字符串)，FieldContent(字符串). ContainerCustomFields实体存​​储Container实体的用户定义的自定义字段.数据如下:

I have a second entity called ContainerCustomFields that has three properties: CustomFieldID (Integer), ContainerID (Integer and Foreign Key), FieldName (String), FieldContent (String). The ContainerCustomFields entity stores user-defined custom fields for the Container entity. The data looks like this:

对于容器:

Container --------- ID | Number | Title | Date | ===================================================== 1 | 10000 | 1st Title | 1/12/2017 | 2 | 10543 | 2nd Title | 1/31/2017 | 3 | 10667 | 3rd Title | 4/12/2016 | 4 | 12889 | 4th Title | 5/23/2012 |

对于自定义字段:

ID | ContainerID | FieldName | Content | ===================================================== 1 | 1 | Colour | Blue | 2 | 1 | Height | 5000 | 3 | 1 | Length | 9100 | 4 | 4 | Colour | Gray |

是否可以返回数据，使其看起来像这样:

Is it possible to return the data so it looks like this:

Container --------- ID | Number | Title | Date | Colour | Height | Length | ==================================================================================== 1 | 10000 | 1st Title | 1/12/2017 | Blue | 5000 | 9100 | 2 | 10543 | 2nd Title | 1/31/2017 | | | | 3 | 10667 | 3rd Title | 4/12/2016 | | | | 4 | 12889 | 4th Title | 5/23/2012 | Gray | | |

我可以获取每个容器的自定义字段及其值的列表，并可以使用select语句从实体中选择所需的列.如何合并这两个语句?

I'm able to get a list of the custom fields and their values for each container as well as select the columns that I want from the entity using a select statement. How do I combine these two statements?

For Each customField In _db.ContainerCustomFields .Where(Function(cf) cf.ContainerID = newBox.ContainerID) returnContainers.Select(Function(c) New With {.ContainerID = c.ContainerID, .Number = c.ContainerNumber, .Title = c.Title, .Date = c.Date })

我正在使用Web API 2将此返回为我的Web应用程序的匿名类型.

I'm using Web API 2 to return this as an anonymous type for my web application.

推荐答案

将第一个组放入每个容器，将字段&值放入grps内的数组中:

First group into each Container, turning Fields & values into arrays within the grps:

var grps = from c in _db.Containers join ccf in _db.ContainerCustomFields on c.ContainerID equals ccf.ContainerID select new {ContainerId = c.ContainerID, ContainerNumber= c.ContainerNumber, Title = c.Title, Date = c.Date, FieldName = ccf.FieldName, FieldContent = ccf.FieldContent} into f group f by f.ContainerId into myGroup select new { ContainerId = myGroup.Key, ContainerNumber = myGroup.Max(d => d.ContainerNumber), Title = myGroup.Max(d => d.Title), Date = myGroup.Max(d => d.Date), Fields = myGroup.Select(d => d.FieldName).ToArray(), Values = myGroup.Select(d => d.FieldContent).ToArray() };

现在找到所有可能出现的字段名称:

Now find all of the Field Names that can appear:

var fields = from ccf in _db.ContainerCustomFields group ccf by ccf.FieldName into grp select grp.Key;

现在创建一个数据表，其中包含我们容器的列和所有可能的字段:

Now create a datatable that contains columns for our containers and all possible fields:

DataTable dt = new DataTable(); dt.Columns.Add("ContainerId", typeof(Int32)); dt.Columns.Add("ContainerNumber", typeof(String)); dt.Columns.Add("Title", typeof(String)); dt.Columns.Add("Date", typeof(DateTime)); foreach(var fld in fields) { dt.Columns.Add(fld, typeof(String)); }

现在为每组数据在我们的数据表中添加一行，并为每个相关字段添加值:

Now for each group of data add a row for to our datatable and add values for each fields that are relevant:

foreach (var row in grps) { DataRow dr = dt.NewRow(); dr["ContainerId"] = row.ContainerId; dr["ContainerNumber"] = row.ContainerNumber; dr["Title"] = row.Title; dr["Date"] = row.Date; for (int i = 0; i < row.Fields.Count(); i++) { dr[row.Fields[i]] = row.Values[i]; } dt.Rows.Add(dr); }

现在，您可以使用数据表填写网格或其他内容.数据表中的缺少字段值将设置为null.

Now you can use the data table to fill out a grid or whatever. Missing Field Values in the data table will be set to null.

我用于数据库表的设置代码:

Setup code I used for the database tables:

CREATE TABLE Container ( ContainerID Int, ContainerNumber Varchar(50), Title Varchar(50), [Date] Date ) INSERT INTO Container VALUES (1, '10000', '1st Title', '2017-01-12'), (2, '10543', '2nd Title', '2017-01-31'), (3, '10667', '3rd Title', '2017-04-12'), (4, '12889', '4th Title', '2017-05-23') CREATE TABLE ContainerCustomFields ( CustomFieldID INT, ContainerID Int, FieldName Varchar(50), FieldContent Varchar(50) ) INSERT INTO ContainerCustomFields VALUES (1,1, 'Colour', 'Blue'), (2,1, 'Height', '5000'), (3,1, 'length', '9100'), (4,4, 'Colour', 'Gray')