在LINQ to Entity Dynamic Select中显示自定义字段 [英] Displaying Custom Fields in 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')

这篇关于在LINQ to Entity Dynamic Select中显示自定义字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！