Mapping not working correctly (ASP.NET)

Question

I need to get all Proposals that related to people

I have several tables in db. It's AspNetUsers, UserToRegion,Region,Cities, Projects and proposals.

Here is model for

AspNetUsers

https://pastebin.com/xts1Xh8m

It connecting with regions with table UserToRegions

Here is it model

https://pastebin.com/8PnBuqf1

So One Region can have several Users

Here is Region Model

https://pastebin.com/9GS9Qst7

City is related to Region

So here is model for City

https://pastebin.com/VWjT0V9h

And Project related to City

So here is Project model

https://pastebin.com/ziE3Sb9C

I tried to get data for project and proposal (Proposal related on project)

Like this on Controller

 public JsonResult Index(string email)
    {
        var id = db.AspNetUsers.Where(x=> x.Email == email).FirstOrDefault();

        string id_val = id.Id;




        var proposals = db.UserToRegions.Where(x=> x.User_Id == id_val)
            .Include(u => u.AspNetUser).Include(u => u.Region).Include(u=>u.Region.Cities)
            .Select(x=> new {
                 Project = x.Region.Cities.,
                 WorkTime = x.WorkTime,
                 Quantity = x.Quantity,
                 Price = x.Price,
                 Service = x.Service.Name,
                 DateFrom = x.Date,
                 DateTo = x.Date_to,
                 WorkTimeTo = x.WorkTimeTo,
                 Id = x.Id,
                 EditingDate = x.CreatingDate

            })
            .ToList();
        return Json(proposals, JsonRequestBehavior.AllowGet);
    }

But in this line Project = x.Region.Cities., It cannot see Projects

Here is Diagram iа it will be easier

Where is my trouble?

UPDATE I rewrite method like this

 var proposals = db.Proposals.Where(x=> x.Project.City.Region.UserToRegions)

            .Select(x=> new {
                 Project = x.Region.Cities.,
                 WorkTime = x.WorkTime,
                 Quantity = x.Quantity,
                 Price = x.Price,
                 Service = x.Service.Name,
                 DateFrom = x.Date,
                 DateTo = x.Date_to,
                 WorkTimeTo = x.WorkTimeTo,
                 Id = x.Id,
                 EditingDate = x.CreatingDate

            })
            .ToList();
        return Json(proposals, JsonRequestBehavior.AllowGet);
    }

And now, I not see UserToRegions.UserId.

Answer 1

Your lambda Expression is combining multiple table via join operation but you are passing only where() condition without parameter to join all other table say userid but instead join multiple table with some parameter like this

   var UserInRole = db.UserProfiles.
    Join(db.UsersInRoles, u => u.UserId, uir => uir.UserId,
    (u, uir) => new { u, uir }).
    Join(db.Roles, r => r.uir.RoleId, ro => ro.RoleId, (r, ro) => new { r, ro })
    .Where(m => m.r.u.UserId == 1)
    .Select (m => new AddUserToRole
    {
        UserName = m.r.u.UserName,
        RoleName = m.ro.RoleName
    });

You can refer this one to solve your issue like here

Answer 2

Cities on the region is a collection and as such you would need to add a .FirstOrDefault() on Cities to get to a collection of projects on a city. But that would only get you projects for the first city in the region which is probably not what you want.

If you are trying to return all the projects in a region you would be better off using explicit joins and changing your approach slightly.

Here’s a starting place for you:

http://www.dotnettricks.com/learn/linq/sql-joins-with-csharp-linq