如何在表關系C#ASP.NETEntityFrameworkCore6WebAPI中的LINQ方法語法中按表名對列進行分組？-有解無憂

我是 C# ASP.Net Entity Framework 的新手。我正在嘗試構建一個 API，我想產生這樣的輸出：

[
    {
        
        "userId": 1275,
        "username": "dmartin",
        "email": "[email protected]",
        "firstName": "Dan",
        "middleInitial": "",
        "lastName": "Martin",
        "isApproved": true,
        "lastActivityDate": "2012-10-05T12:23:24.253",
        "lastLoginDate": "2021-10-27T09:13:56.597",
        "lastPasswordChangedDate": "2020-07-29T16:06:41.863",
        "creationDate": "2010-04-07T22:51:14",
        "Iscinstance":[
            {
                "iscinstanceId": 236,
                "name": "ABA"
            }
        ],
        "UserProfile":[
            {
                "profileName": "",
                "address1": "",
                "directConnectPhone": "",
                "profileEmail": "",
                "profile": null
            }
        ]
    }
]

A 使用表關系創建此類：

用戶表

[DataContract]
public partial class User
{
    [DataMember]
    public int UserId { get; set; }
    [DataMember]
    public string Username { get; set; }
    [DataMember]
    public string Email { get; set; }
    [DataMember]
    public bool IsApproved { get; set; }
    [DataMember]
    public DateTime? LastActivityDate { get; set; }
    [DataMember]
    public DateTime? LastLoginDate { get; set; }
    [DataMember]
    public DateTime? LastPasswordChangedDate { get; set; }
    [DataMember]
    public DateTime? CreationDate { get; set; }
    [DataMember]
    public string FirstName { get; set; }
    [DataMember]
    public string LastName { get; set; }
    [DataMember]
    public string MiddleInitial { get; set; }

    public virtual UserProfile Profile { get; set; }
    public virtual LinkUsersToIscinstance LinkUsersToIscinstances { get; set; }
    
}

用戶資料表

[DataContract]
public partial class UserProfile
{
    public UserProfile()
    {
        LinkUsersToIscinstances = new HashSet<LinkUsersToIscinstance>();
        Users = new HashSet<User>();
    }
    [DataMember]
    public int ProfileId { get; set; }
    [DataMember]
    public string ProfileName { get; set; }
    [DataMember]
    public string Email { get; set; }
    [DataMember]
    public string DirectConnectPhone { get; set; }
    [DataMember]
    public string EmergencyContactNumber { get; set; }
    [DataMember]
    public string Address1 { get; set; }
    [DataMember]
    public string Address2 { get; set; }
    [DataMember]
    public string County { get; set; }
    [DataMember]
    public int State { get; set; }
    [DataMember]
    public string Zip { get; set; }
    [DataMember]
    public string EmergencyCell { get; set; }
    
    public virtual ICollection<LinkUsersToIscinstance> LinkUsersToIscinstances { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

LinkUsersToIscinstance 表

[DataContract(IsReference = true)]
public partial class LinkUsersToIscinstance
{
    public LinkUsersToIscinstance()
    {
        Users = new HashSet<User>();
    }
    public int LinkId { get; set; }
    [DataMember]
    public int IscinstanceId { get; set; }
    [DataMember]
    public int UserId { get; set; }

    public virtual UserProfile Profile { get; set; }
    public virtual Iscinstance Iscinstances { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

清點表

[DataContract]
public partial class Iscinstance
{
    public Iscinstance()
    {
        LinkUsersToIscinstances = new HashSet<LinkUsersToIscinstance>();
    }
    [DataMember]
    public int IscinstanceId { get; set; }
    [DataMember]
    public string Name { get; set; }

    public virtual ICollection<LinkUsersToIscinstance> LinkUsersToIscinstances { get; set; }
}

我還創建了一個自定義類，但我認為不需要它，這里是：

[DataContract]
public class CustomClass
{
    [DataMember]
    public int IscinstanceId { get; set; }
    //[DataMember(Name = "ISCInstanceName")]
    [DataMember]
    public string? Name { get; set; }
    [DataMember]
    public int UserId { get; set; }
    [DataMember]
    public string? Username { get; set; }
    [DataMember]
    public string? Email { get; set; }
    [DataMember]
    public string? FirstName { get; set; }
    [DataMember]
    public string? MiddleInitial { get; set; }
    [DataMember]
    public string? LastName { get; set; }
    [DataMember]
    public bool IsApproved { get; set; }
    [DataMember]
    public DateTime? LastActivityDate { get; set; }
    [DataMember]
    public DateTime? LastLoginDate { get; set; }
    [DataMember]
    public DateTime? LastPasswordChangedDate { get; set; }
    [DataMember]
    public DateTime? CreationDate { get; set; }
    [DataMember]
    public string? ProfileName { get; internal set; }
    [DataMember]
    public string? Address1 { get; internal set; }
    [DataMember]
    public string? DirectConnectPhone { get; internal set; }
    [DataMember]
    public string? ProfileEmail { get; internal set; }
}

這是我的查詢：

 var customer = await _context.Users
                            .Where(c => c.UserId  == UserId && c.LastActivityDate > date && c.IsApproved == num)
                            .Include(c => c.Profile)
                            .Include(c => c.LinkUsersToIscinstances).ThenInclude(c => c.Iscinstances)
                            .OrderBy(c => c.LinkUsersToIscinstances.Iscinstances.Name).ThenBy(c => c.LastName).ThenBy(c => c.FirstName)
                            .Select(c => new CustomClass
                            {
                                IscinstanceId = c.LinkUsersToIscinstances.Iscinstances.IscinstanceId,
                                Name = c.LinkUsersToIscinstances.Iscinstances.Name,

                                UserId = c.UserId,
                                Username = c.Username,
                                Email = c.Email,
                                FirstName= c.FirstName,
                                MiddleInitial= c.MiddleInitial,
                                LastName= c.LastName,
                                IsApproved= c.IsApproved,
                                LastActivityDate= c.LastActivityDate,
                                LastLoginDate= c.LastLoginDate,
                                LastPasswordChangedDate= c.LastPasswordChangedDate,
                                CreationDate= c.CreationDate,

                                ProfileName = c.Profile.ProfileName,
                                ProfileEmail = c.Profile.Email,
                                DirectConnectPhone = c.Profile.DirectConnectPhone,
                                Address1 = c.Profile.Address1

                                })
                            .ToListAsync();

這是我當前的輸出

[
    {
        "iscinstanceId": 236,
        "name": "ABA",
        "userId": 1275,
        "username": "dmartin",
        "email": "[email protected]",
        "firstName": "Dan",
        "middleInitial": "",
        "lastName": "Martin",
        "isApproved": true,
        "lastActivityDate": "2012-10-05T12:23:24.253",
        "lastLoginDate": "2021-10-27T09:13:56.597",
        "lastPasswordChangedDate": "2020-07-29T16:06:41.863",
        "creationDate": "2010-04-07T22:51:14",
        "profileName": "",
        "address1": "",
        "directConnectPhone": "",
        "profileEmail": "",
        "profile": null
    }
]

希望有人可以幫助我:(

uj5u.com熱心網友回復：

首先，您需要一些類來表示預期的 json 結果：

public class UserModel
{
    public int UserId { get; set; }
    public string? Username { get; set; }
    public string? Email { get; set; }
    public string? FirstName { get; set; }
    public string? MiddleInitial { get; set; }
    public string? LastName { get; set; }
    public bool IsApproved { get; set; }
    public DateTime? LastActivityDate { get; set; }
    public DateTime? LastLoginDate { get; set; }
    public DateTime? LastPasswordChangedDate { get; set; }
    public DateTime? CreationDate { get; set; }
    public IEnumerable<IscinstanceModel> Iscinstance { get; set; }
    public IEnumerable<UserProfileModel> UserProfile { get; set; }
}

public class IscinstanceModel
{
    public int IscinstanceId { get; set; }
    public string? Name { get; set; }
}

public class UserProfileModel
{
    public string? ProfileName { get; set; }
    public string? Address1 { get; set; }
    public string? DirectConnectPhone { get; set; }
    public string? ProfilEmail { get; set; }
    public string? Profile { get; set; }
}

然后您可以將物體轉換為 api 模型，例如：

var customers = await _context.Users
    .Where(c => c.UserId  == UserId && c.LastActivityDate > date && c.IsApproved == num)
    .Include(c => c.Profile)
    .Include(c => c.LinkUsersToIscinstances).ThenInclude(c => c.Iscinstances)
    .OrderBy(c => c.LinkUsersToIscinstances.Iscinstances.Name).ThenBy(c => c.LastName).ThenBy(c => c.FirstName)
    .Select(c => new UserModel
    {
        UserId = c.UserId,
        Username = c.Username,
        Email = c.Email,
        FirstName = c.FirstName,
        MiddleInitial = c.MiddleInitial,
        LastName = c.LastName,
        IsApproved = c.IsApproved,
        LastActivityDate = c.LastActivityDate,
        LastLoginDate = c.LastLoginDate,
        LastPasswordChangedDate = c.LastPasswordChangedDate,
        CreationDate = c.CreationDate,
        Iscinstance = c.LinkUsersToIscinstances
            .SelectMany(l => l.Iscinstances)
            .Select(i =>
                new IscinstanceModel {
                    IscinstanceId = l.IscinstanceId,
                    Name = l.name
                }
            ),
        UserProfile = new [] {
            new UserProfileModel{
                ProfileName = c.Profile.ProfileName,
                Address1 = c.Profile.Address1,
                DirectConnectPhone = c.Profile.DirectConnectPhone,
                ProfilEmail = c.Profile.ProfilEmail
            }
        }
    })
    .ToListAsync();

The expected json has by user a list of profile, but the entity has one profile. In this case, just encapsulate the profile in a collection. Same to iscinstance.

uj5u.com熱心網友回復：

從您的后端代碼來看，似乎User有一個UserProfileand Iscinstance，但您預期的 json 是一個List 物件：

"Iscinstance":[
        {
            "iscinstanceId": 236,
            "name": "ABA"
        }
    ],
    "UserProfile":[
        {
            "profileName": "",
            "address1": "",
            "directConnectPhone": "",
            "profileEmail": "",
            "profile": null
        }
    ]

如果您確實想要這種型別的 json，則需要將您的更改CustomClass為：

public class CustomClass
{
    public int UserId { get; set; }
    public string? Username { get; set; }
    public string? Email { get; set; }
    public string? FirstName { get; set; }
    public string? MiddleInitial { get; set; }
    public string? LastName { get; set; }
    public bool IsApproved { get; set; }
    public DateTime? LastActivityDate { get; set; }
    public DateTime? LastLoginDate { get; set; }
    public DateTime? LastPasswordChangedDate { get; set; }
    public DateTime? CreationDate { get; set; }
    public dynamic? Iscinstance { get; set; }
    public dynamic? UserProfile { get; set; }
}

控制器：

    var customer = await _context.User
                .Where(c => c.UserId  == UserId && c.LastActivityDate > date && c.IsApproved == num)
                
                .OrderBy(c => c.LinkUsersToIscinstances.Iscinstances.Name).ThenBy(c => c.LastName).ThenBy(c => c.FirstName)
                .Select(c => new TestModel
                {
                    UserId = c.UserId,
                    Username = c.Username,
                    Email = c.Email,
                    FirstName = c.FirstName,
                    MiddleInitial = c.MiddleInitial,
                    LastName = c.LastName,
                    IsApproved = c.IsApproved,
                    LastActivityDate = c.LastActivityDate,
                    LastLoginDate = c.LastLoginDate,
                    LastPasswordChangedDate = c.LastPasswordChangedDate,
                    CreationDate = c.CreationDate,
                    Iscinstance = new [] {
                        new {
                            IscinstanceId= c.LinkUsersToIscinstances.Iscinstances.IscinstanceId,
                            Name = c.LinkUsersToIscinstances.Iscinstances.Name
                        }
                    },
                    UserProfile = new []{
                        new {
                            ProfileName = c.Profile.ProfileName,
                            ProfileEmail = c.Profile.Email,
                            DirectConnectPhone = c.Profile.DirectConnectPhone,
                            Address1 = c.Profile.Address1
                        }
                    }
                })
                .ToListAsync();

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/457069.html

標籤：C＃网 asp.net 核心 asp.net-web-api linq 方法语法

上一篇：如何使用ConfigurationBuilder決議現有的json字串（不是檔案）

下一篇：在GoogleCloudStorage中洗掉檔案或上傳同名的新檔案后，檔案仍可訪問