linq 从嵌套表中获取数据

kgsdhlau 于 2023-01-03 发布在其他

关注(0)|答案(2)|浏览(143)

我尝试使用LINQ命令获取嵌套表，但无法返回所需的输出。
给定IdTrip，我需要获取行程数据和数组形式的客户/国家数据。
我试着使用下面的命令，我设法得到了国家（即使我应该只显示名称），但是我不能像预期的那样返回客户端对象，只能返回表Client_Trips中的id。

IEnumerable<object> countries = await _db.Trips
    .Include(t => t.IdCountries)
    .Include(d => d.ClientTrips)
    .Select(c => new
    {
        Name = c.Name,
        Description = c.Description,
        DateFrom = c.DateFrom,
        DatTo = c.DateTo,
        MaxPeople = c.MaxPeople,
        Countries = c.IdCountries,
        Clients = c.ClientTrips
    }).ToListAsync();

这是我希望收到的：

[
    {
        "name": "XXXX",
        "description": "XXXXXX",
        "dateFrom": "2023-01-12T00:00:00",
        "datTo": "2023-01-17T00:00:00",
        "maxPeople": 10,
        "countries": [
            {
                "name": "XXXX"
            },
            {
                "name": "XXXX"
            }
        ],
        "clients": [
            {
                FirstName: "name",
                LastName: "lastname"
            },
                {
                FirstName: "name",
                LastName: "lastname"
            }
        ]
    }   
]

linq

来源：https://stackoverflow.com/questions/74965240/get-data-from-nested-tables

2条答案

按热度按时间

qxgroojn1#

如果导航属性正确，查询应如下所示：

var countries = await _db.Trips
    .Select(c => new
    {
        Name = c.Name,
        Description = c.Description,
        DateFrom = c.DateFrom,
        DatTo = c.DateTo,
        MaxPeople = c.MaxPeople,
        Countries = c.CountryTrips.Select(x => new { x.Country.Name }).ToArray(),
        Clients = c.ClientTrips.Select(x => new { x.Client.FirstName, x.Client.Lastname }).ToArray()
    }).ToListAsync();

赞(0）回复(0）举报 2023-01-03

c9x0cxw02#

为了完整起见，我在下面添加两个解决方案：

*仅使用一个LINQ查询的高效方法

变量国家= await _db.Trips

.Select(c => new
     {
         Name = c.Name,
         Description = c.Description,
         DateFrom = c.DateFrom,
         DatTo = c.DateTo,
         MaxPeople = c.MaxPeople,
         Countries = c.IdCountries.Select(x => new { x.Name 
      }).ToArray(),
             Clients = c.ClientTrips.Select(x => new { x.IdClientNavigation.FirstName, x.IdClientNavigation.LastName }).ToArray()
         }).ToListAsync();

*不是有效的方法，但确实有效-运行两个单独的查询，并将输出分配给自定义类“Response”的对象

公共类TripResponse {公共字符串名称{ get;设置; }

public string? Description { get; set; }
    public DateTime DateFrom { get; set; }
    public DateTime DateTo { get; set; }
    public int MaxPeople { get; set; }
    public IEnumerable<object> Countries { get; set; }

    public IEnumerable<object> Clients { get; set; }}



            var clients = from client in _db.Clients
                      join clientTrip in _db.ClientTrips on client.IdClient equals clientTrip.IdClient
                      join trip in _db.Trips.Where(c => c.IdTrip == t.IdTrip) on clientTrip.IdTrip equals trip.IdTrip
                      select new { FirstName = client.FirstName, LastName = client.LastName };

        var countries = await _db.Trips.Where(c => c.IdTrip == t.IdTrip).Select(c => c.IdCountries.Select(c => new { Name = c.Name })).ToListAsync();
        TripResponse response = new TripResponse
        {
            Name = t.Name,
            Description = t.Description,
            DateFrom = t.DateFrom,
            DateTo = t.DateTo,
            MaxPeople = t.MaxPeople,
            Countries = countries,
            Clients = clients

        };

赞(0）回复(0）举报 2023-01-03

我来回答

linq 从嵌套表中获取数据

2条答案

相关问题

热门标签

最新问答