CODEWITHSUNDEEP
jsonasp.net-mvcentity-frameworklinqcode-first
duke
duke

Reputation: 1874

fetch data from associated tables having foreign key in between them

there are two tables named project and city like this:

   public class Project
   { 
    [Key]
    public int ProjectId { get; set; }
    public string ProjectName { get; set; }
    public int CityID { get; set; }
    public City City { get; set; }
   }

   public class City
   {
    [Key]
    public int CityID { get; set; }
    public string CityName { get; set; }
    public ICollection<Project> Projects { get; set; }
   }

Here, CityID in the project class is the foreign key to City class joining both tables.I have properly configured and checked that both are inter connected to each other. Now, i have a Json action method to fetch the Desired property like this:

   public JsonResult GetProjects()
    {
        var ret = (from project in db.Projects.ToList()
                   orderby project.ProjectId
                   select new
                   {
                       CityName = project.City.CityName,
                       ProjectId = project.ProjectId   
                   }).AsEnumerable();
        return Json(ret, JsonRequestBehavior.AllowGet);
    }

here, i am trying to push out the cityName but i am unable to get back cityname.It is giving System.NullRefernceException at line CityName = project.City.CityName.Please suggest me what to do now. Is something wrong in my code. i am able to get other properties.

Upvotes: 0

Views: 122

Answers (1)

JotaBe
JotaBe

Reputation: 39055

Whe you use somehting like .ToList(), .ToArray(), and so on, you are materializing the query. In LINQ to EF materailizing the query means running the SQL query and populating your classes with the data received form the DB. From that point on, there is no chance that the following referred properties are retrieved from the dtabase.

LINQ to EF uses IQueryable<T> interface. An IQueryable<T> is a "potential query" that has not already been "executed". While you do things that doesn't materialize the query, your query will keep being a IQueryable<T> and it won't be executed in the DB.

There is also another thing that materializes the queryable: enumerating it.

So, what you need to is to not materialize the query until you have provided all the information necessary to run the query. In this case you have to remove the .ToList()

Upvotes: 2

Related Questions