无法获得剃刀页面的多对多导航，以从剑道网格的右侧表格返回数据

Question

不确定我说的对不对，但我在net core 3.1中有一个剃刀页面业务应用程序，有相当多的多对多连接。我现在只处理一个联接，并试图通过存储库调用将结果馈送到剑道网格以下是示例的模型，它是由"NotesTypeJoins"连接的"Notes"one_answers"NotesTypes">

public class Note
{
public int ID { get; set; }
[Display(Name = "Short Title")]
[Required]
public String Title { get; set; }
[Display(Name = "Note Content")]
public String NoteText { get; set; }
public int UserID { get; set; }
//some fields left out for brevity
public ICollection<NoteStaffJoin> NoteStaffJoins { get; set; }
public ICollection<NoteClientJoin> NoteClientJoins { get; set; }
public ICollection<NoteOrgJoin> NoteOrgJoins { get; set; }
public ICollection<NoteNoteTypeJoin> NoteNoteTypeJoins { get; set; }
}
public class NoteNoteTypeJoin
{
public int NoteNoteTypeJoinID { get; set; }
public int NoteID { get; set; }
public int NoteTypeID { get; set; }
public DateTime? Saved { get; set; }
public int? UserID { get; set; }
public Note Note { get; set; }
public NoteType NoteType { get; set; }

}
public class NoteType
{
public int ID { get; set; }
public string Type { get; set; }
public int? ParentID { get; set; }
public int? NoteTypeGroupID { get; set; }
public int? DisplayOrder { get; set; }
public int? UserID { get; set; }
public ICollection<NoteNoteTypeJoin> NoteNoteTypeJoins { get; set; }
}
public FliveRetryContext(DbContextOptions<FliveRetryContext> options)
: base(options)
{
public DbSet<FliveRetry.Models.Note> Note { get; set; }
public DbSet<FliveRetry.Models.NoteType> NoteType { get; set; }
public DbSet<FliveRetry.Models.NoteNoteTypeJoin> NoteNoteTypeJoin { get; set; }
}

据我所见，外键等都是通过ef核心正确定义的

CREATE TABLE [dbo].[NoteNoteTypeJoin] (
[NoteNoteTypeJoinID] INT           IDENTITY (1, 1) NOT NULL,
[NoteID]             INT           NOT NULL,
[NoteTypeID]         INT           NOT NULL,
[Saved]              DATETIME2 (7) NULL,
[UserID]             INT           NULL,
CONSTRAINT [PK_NoteNoteTypeJoin] PRIMARY KEY CLUSTERED ([NoteNoteTypeJoinID] ASC),
CONSTRAINT [FK_NoteNoteTypeJoin_Note_NoteID] FOREIGN KEY ([NoteID]) REFERENCES [dbo].[Note] ([ID]) ON DELETE CASCADE,
CONSTRAINT [FK_NoteNoteTypeJoin_NoteType_NoteTypeID] FOREIGN KEY ([NoteTypeID]) REFERENCES [dbo].[NoteType] ([ID]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_NoteNoteTypeJoin_NoteID]
ON [dbo].[NoteNoteTypeJoin]([NoteID] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_NoteNoteTypeJoin_NoteTypeID]
ON [dbo].[NoteNoteTypeJoin]([NoteTypeID] ASC);

上周我在网上试过了每一个例子，但都做不好。我最终使用联接表中的ID实现了这一点，该表不需要调用NoteTypes表：

public async Task<List<Note>> GetAllNotesForNoteTypeIDAsync(int notetypeid)
{
var noteData = await context.Note
.Include(n => n.NoteNoteTypeJoins)
.ThenInclude(t => t.NoteType)
.Where(i => i.NoteNoteTypeJoins.Any(x => x.NoteTypeID == notetypeid))
.ToListAsync();
return noteData;
}

我不得不在startup.cs中添加这个引用的显式选项，以获得包含，感谢Telerik提供的信息：

services.AddRazorPages().AddNewtonsoftJson
(options => {
options.SerializerSettings.ContractResolver = new DefaultContractResolver();
options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
});

同样，这也适用于仅查询左手表，即使包括其他表：

public async Task<List<Note>> GetAllNotesForUserAsync(int userId)
{
var noteData = await context.Note
.Include(n => n.NoteNoteTypeJoins)
.ThenInclude(t => t.NoteType)
.Where(i => i.UserID == userId)
.ToListAsync();
return noteData;
}

我想按NoteType文本搜索，而不是按ID搜索，因为不同版本的ID可能不同。这就是我目前所拥有的，但它会崩溃页面，就像许多其他变体一样：

public async Task<List<Note>> GetAllNotesForNoteTypeAsync(string notetype)
{            
var noteData = await context.Note
.Include(n => n.NoteNoteTypeJoins)
.ThenInclude(t => t.NoteType)
.Where(i => i.NoteNoteTypeJoins.Any(x => x.NoteType.Type.ToString() == notetype))
.ToListAsync();
return noteData;
}

这会导致以下消息崩溃：

An unhandled exception occurred while processing the request.
InvalidOperationException: The LINQ expression 'DbSet<NoteNoteTypeJoin>
.Where(n0 => EF.Property<Nullable<int>>((EntityShaperExpression: 
EntityType: Note
ValueBufferExpression: 
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False
), "ID") != null && EF.Property<Nullable<int>>((EntityShaperExpression: 
EntityType: Note
ValueBufferExpression: 
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False
), "ID") == EF.Property<Nullable<int>>(n0, "NoteID"))
.Join(
outer: DbSet<NoteType>, 
inner: n0 => EF.Property<Nullable<int>>(n0, "NoteTypeID"), 
outerKeySelector: n1 => EF.Property<Nullable<int>>(n1, "ID"), 
innerKeySelector: (o, i) => new TransparentIdentifier<NoteNoteTypeJoin, NoteType>(
Outer = o, 
Inner = i
))
.Any(n0 => n0.Inner.Type.ToString() == __notetype_0)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

我也无法访问最后一次加入，这让我无法在论坛上使用很多建议。其中(n=>n.NoteNoteTypeJoins.Type==notetype((不可用，原因是。NoteNoteTypeJoins之后类型不可用。

以下是我需要筛选的四种多对多关系：

Note = await _context.Note
.Include(i => i.NoteOrgJoins).ThenInclude(i => i.Org)
.Include(i => i.NoteClientJoins).ThenInclude(i => i.Client)
.Include(i => i.NoteStaffJoins).ThenInclude(i => i.Staff)
.Include(i => i.NoteNoteTypeJoins).ThenInclude(i => i.NoteType)

如果有人能建议一个工作导航呼叫而不是加入，这可以让我摆脱麻烦，至少在音符类型的呼叫中，我会很兴奋。如果你能告诉我如何添加其他呼叫，那会更好。我仍在学习C#和razor，所以它可能超出了调用本身，但我一直试图坚持标准设置。

感谢所有

Answer 1

我也无法访问上次加入，这让我无法在论坛上使用很多建议。其中(n=>n.NoteNoteTypeJoins.Type==notetype((不可用，原因是。NoteNoteTypeJoins之后类型不可用。

原因是EF Core 3中禁用了隐式客户端评估。

参考：

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-不再在客户端上评估查询

你可以更改你的代码如下：

var noteData = await _context.Note
.Include(n => n.NoteNoteTypeJoins)
.ThenInclude(t => t.NoteType)                                    
.ToListAsync();
var d = noteData.Where(i => i.NoteNoteTypeJoins.Any(x => x.NoteType.Type.ToString() == notetype))
.ToList();

另一种方法是NoteType类中的Type属性是字符串类型。无需使用ToString()方法：

var noteData = await _context.Note
.Include(n => n.NoteNoteTypeJoins)
.ThenInclude(t => t.NoteType)
.Where(i => i.NoteNoteTypeJoins.Any(x => x.NoteType.Type == notetype))
.ToListAsync();