搜索表ASP.NET核心的相关值

Question

你好，我有两个来自外部数据源(MSSQL)的表。表格"；游戏"；以及"；流派"；。我想实现按类型搜索游戏。我有一个函数可以按游戏名称搜索游戏，如果技术上可能的话，我想要这样的(OnGetAsync()方法)。感谢

/游戏/索引.cs:

public class IndexModel : PageModel
{
private readonly GamesData.Data.GamesDataContext _context;
public IndexModel(GamesData.Data.GamesDataContext context)
{
_context = context;
}

public IList<GamesTable> GamesTable { get; set; }

[BindProperty(SupportsGet = true)]
public string SearchGames { get; set; }

public SelectList GenresList { get; set; }
[BindProperty(SupportsGet = true)]
public string SelectGenre { get; set; }

public async Task OnGetAsync()
{

var searchGame = from m in _context.gamesTable
select m;
if (!string.IsNullOrEmpty(SearchGames))
{
searchGame = searchGame.Where(s => s.NameGame.Contains(SearchGames));
}
IQueryable<string> searchGenres = from m in _context.genresTable
select m.NameGenres;
if (!string.IsNullOrEmpty(SelectGenre))
{
searchGame = searchGame.Where(x => x.NameGame == SelectGenre); // presumably a correct Join needs to go through here, but the problem is it doesn't want to write the joined table to the table "Games"
}
GamesTable = await searchGame.ToListAsync();
GenresList = new SelectList(await searchGenres.Distinct().ToListAsync());        
}
}

型号：

public class GamesTable
{
public int ID { get; set; }
[Required]
[Display(Name = "Name Game")]
[StringLength(50)]
public string NameGame { get; set; }
[Required]
[Display(Name = "Name Studio ")]
[StringLength(50)]
public string NameStudio { get; set; }
[Display(Name = "Relize Date ")]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[DataType(DataType.Date)]
public DateTime RelizeDate { get; set; }
}
//таблица GenresTable
public class GenresTable
{
public int ID { get; set; }
[Display(Name = "Name Genre ")]
public string NameGenres { get; set; }
}
public class GameGenre
{    
public int ID { get; set; }
public int IdGenre { get; set; }
public int IdGame { get; set; }
public string NameGame { get; set; }
public string NameGenres { get; set; }
}

Answer 1

我会稍微更改一下模型/类：

在GamesGenre中，我会用这个替换所有内容(并将GenresTableId + GamesTableId作为我的主密钥，但你也可以使用你的Id)：

public int GenresTableId { get; set; }
public GenresTable GenresTable { get; set; }
public int GamesTableId { get; set; }
public GamesTable GamesTable{ get; set; }

在GenresTable中，添加

//One genre can be referenced in multiple GamesGenre (genres with multiple games)
public ICollection<GamesGenre> GamesGenre{ get; set; }

在游戏表中，添加：

//One game can be referenced in multiple GamesGenre (game with multiple genres)
public ICollection<GamesGenre> GamesGenre{ get; set; }

然后你可以做一些类似的事情：

var gamesByGenre = _context.GamesGenre.Where(gg=> gg.GenresTable.NameGenres == "MyGenre").Select(gg=> gg.GamesTable).ToList();
//or
var genresOnMyGame = _context.GamesGenre.Where(gg=> gg.GamesTable.NameGame== "MyGame").Select(gg=> gg.GenresTable).ToList();

此外，顺便说一句，我会改变你的命名风格：

ID changes to Id

NameGame changes to Name在桌上游戏中，去Game.Name更有逻辑；Genre.Name等…

IdGenre changes to GenreId