Hello I have two tables from an external data source (MSSQL). Tables "Games" and "Genres". I want to implement a search for games by genre. I have a function to search for a game by its name and would like something like this (OnGetAsync () method) if technically possible. thanks

/Games/Index.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());        
    }
}

Models:

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; }
}

CodePudding user response：

I would change the models/classes a bit:

In GamesGenre, I would substitute everything by this (and make GenresTableId GamesTableId my primarykey, but you can also use your Id):

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

In GenresTable, add

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

In GamesTable, add:

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

Then you could do something like:

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();

Also, on a sidenote I would change some things in your nomenclature style:

ID changes to Id

NameGame changes to Name in table Games, it has more logic to go Game.Name; Genre.Name, etc...

IdGenre changes to GenreId