Filtering query

Question

can anyone help me. I'm trying to get results from my query but can't get them to work. i'm making query in connection table whitch connects other 3 tables.. and for a result i need to get Radnja.Grad,Radnja.Naziv_radnje,Modeli.Naziv_modela,Dijelovi.Naziv and Dijelovi.Cijena from tables.. here is my tables and connections and code that i got stuck on.

public async Task<IActionResult> trazi(string grad, string model, string dio)
        {
            var query = from veza in db.ModeliRadnjas
                        join dijelovi in db.Dijelovis on veza.IdDijela equals dijelovi.IdDijela
                        join radnja in db.Radnjas on veza.IdRadnje equals radnja.IdRadnje
                        join auto in db.Modelis on veza.IdModela equals auto.IdModela
                        select veza;
            if (!String.IsNullOrEmpty(grad) && !String.IsNullOrEmpty(model) && !String.IsNullOrEmpty(dio))
            {
                query = query.Where(veza => veza.IdRadnjeNavigation.Grad.Equals(grad) && veza.IdModelaNavigation.NazivModela.Equals(model) && veza.IdDijelaNavigation.Naziv.Equals(dio));
            }
            else if (!String.IsNullOrEmpty(model) && !String.IsNullOrEmpty(dio))
            {
                query = query.Where(veza => veza.IdModelaNavigation.NazivModela.Equals(model) && veza.IdDijelaNavigation.Naziv.Equals(dio));
            }
            else if (!String.IsNullOrEmpty(grad) && !String.IsNullOrEmpty(model))
            {
                query = query.Where(veza => veza.IdRadnjeNavigation.Grad.Equals(grad) && veza.IdModelaNavigation.NazivModela.Equals(model));
            }
            else if (!String.IsNullOrEmpty(grad))
            {
                query = query.Where(veza => veza.IdRadnjeNavigation.Grad.Equals(grad));
            }
            else if (!String.IsNullOrEmpty(model))
            {
                query = query.Where(veza => veza.IdModelaNavigation.NazivModela.Equals(model));
            }
            else if (!String.IsNullOrEmpty(dio))
            {
                query = query.Where(veza => veza.IdDijelaNavigation.Naziv.Equals(dio));
            }
            else
            {
                
            }
            return View(await query.ToListAsync());

https://prnt.sc/rjTW5nB2ypB0

Answer 1

If you want to retrieve the database in this way, you need to add the three fields of IdRadnjeNavigation, IdModelaNavigation, IdDijelaNavigation to the ModeliRadnjas table, and you need to gradually retrieve the matching fields.

Below is my test code,it works fine:

Modeli_Radnja.cs:

public class Modeli_Radnja
    {
        [Key]
        public int ID_veze { get; set; }
        public int ID_radnje { get; set; }
        public int ID_modela { get; set; }
        public int ID_dijela { get; set; }

        [ForeignKey(nameof(ID_modela))]
        public Modeli IdModelaNavigation { get; set; }
        [ForeignKey(nameof(ID_dijela))]
        public Dijelovi IdDijelaNavigation { get; set; }
        [ForeignKey(nameof(ID_radnje))]
        public Radnja IdRadnjeNavigation { get; set; }
    }

Controller:

public async Task<IActionResult> trazi(string grad, string model, string dio)
        {
            grad = "TestRadnja";
            model = "TestModeli";
            dio = "TestDijelovi";
            
            var query = from veza in db.Modeli_Radnja
                        join dijelovi in db.Dijelovi on veza.ID_dijela equals dijelovi.ID_dijela
                        join radnja in db.Radnja on veza.ID_radnje equals radnja.ID_radnje
                        join auto in db.Modeli on veza.ID_modela equals auto.ID_modela
                        select veza;
            var modeli_Radnja = from m in db.Modeli_Radnja select m;
            var dijelovi1 = from m in db.Dijelovi select m;
            foreach (var dijelovi in dijelovi1)
            {
                modeli_Radnja.Where(c => c.ID_dijela == dijelovi.ID_dijela);
            }
            var modela1 = from m in db.Modeli select m;
            foreach (var modela in modela1)
            {
                modeli_Radnja.Where(c => c.ID_modela == modela.ID_modela);
            }
            var radnje1 = from m in db.Radnja select m;
            foreach (var radnje in radnje1)
            {
                modeli_Radnja.Where(c => c.ID_radnje == radnje.ID_radnje);
            }


            if (!String.IsNullOrEmpty(grad) && !String.IsNullOrEmpty(model) && !String.IsNullOrEmpty(dio))  
            {
                query = query.Where(veza => veza.IdDijelaNavigation.Naziv.Equals(dio) && veza.IdRadnjeNavigation.Grad.Equals(grad) && veza.IdModelaNavigation.Naziv_modela.Equals(model));
            }
            return Ok(await query.ToListAsync());
        }

Test Result:

Then you can take the value you want from it.