locked
.Net Core Best Practices For Returning Data From Linked (Foreign Key) Table RRS feed

  • Question

  • User-1580299748 posted

    Hello,

    I am using .Net Core 2.2 Razor Pages (NOT MVC), C# with SQL Server and Entity Framework Core.

    I have two classes, a USSTATE.cs and a CITY.cs:  

    STATES (e.g. Ohio) have CITIES (Cleveland).

    public class USState
    {
         [Key]
         public int Id { get; set; }

         [Required]
         public string State { get; set; }

         [Required]
         public string Abbreviation { get; set; }
    }

    public class City
    {
         [Key]
         public int Id { get; set; }

         [Required]
         [Display(Name = "City")]
         public string Description { get; set; }

         public int StateID { get; set; }
         [ForeignKey("StateID")]
         public virtual USState USState { get; set; }

    }

    In the OnGet Handler of my index.cshtml page I am trying to figure out what best practices are to return all rows of STATE/CITY data in my database.

    Here are the options I have considered:

    Create a ViewModel of the data:

    public class StateAndCityViewModel
    {
         public USState State { get; set; }
         public City City { get; set; }
    }

    I believe my second option would be to use a "var =" and LINQ syntax:

    public City City { get; set; }
    public USStates USStates { get; set; }

    var StateAndCity = (from c in City
                       join st in USState
                       on c.Id equals st.Id
                       orderby c.Description
                       select new { c.Description, st.State }).ToList();

    return Page();

    I  have attempted to implement this solution, however, I am getting an error "CS1936 Could not find an implementation of the query pattern for source type City.  'Join' not found."

    I do not completely understand what this error means, and Google has not helped.

    I am sure there are other options that I am not aware of.

    Any guidance and/or advice would be most welcome.

    Thanks in advance for your help!

    Monday, December 16, 2019 7:45 AM

Answers

  • User711641945 posted

    Hi bsashcraft,

    Here is a working sample like below:

    1.Model:

    public class USState
    {
        [Key]
        public int Id { get; set; }
    
        [Required]
        public string State { get; set; }
    
        [Required]
        public string Abbreviation { get; set; }
    }
    
    public class City
    {
        [Key]
        public int Id { get; set; }
    
        [Required]
        [Display(Name = "City")]
        public string Description { get; set; }
    
        public int StateID { get; set; }
        [ForeignKey("StateID")]
        public virtual USState USState { get; set; }
    }

    2.Index.cshtml:

    @page
    @model IndexModel
    
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(model => model.City[0].Description)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.City[0].USState.State)
                </th>
            </tr>
        </thead>
        <tbody>
    @foreach (var item in Model.City) {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Description)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.USState.State)
                </td>           
            </tr>
    }
        </tbody>
    </table>

    3.Index.cshtml.cs:

    public class IndexModel : PageModel
    {
        private readonly RazorProjContext _context;
    
        public IndexModel(RazorProjContext context)
        {
            _context = context;
        }
        public IList<City> City { get;set; }
    
        public async Task OnGetAsync()
        {
            City = await _context.City.Include(c=>c.USState).ToListAsync();
        }
    }

    4.DbContext:

    public class RazorProjContext : DbContext
    {
        public RazorProjContext (DbContextOptions<RazorProjContext> options)
            : base(options)
        {
        }
    
        public DbSet<USState> USState { get; set; }
        public DbSet<City> City { get; set; }
    }

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 17, 2019 9:49 AM

All replies

  • User1120430333 posted

    Based on the code you have shown,  City nor USStates are queryable with both needing to be a collection/List<T>  List<City> and a List<USStates>, becuase List<T> implements  IEnumerable and that makes a List<T> queriable by Linq.  I could be wrong, but for sure City is not queryable it looks to me.

    https://www.tutorialsteacher.com/csharp/csharp-list

    public City City { get; set; }
    public USStates USStates { get; set; }
    
    var StateAndCity = (from c in City
                       join st in USState
                       on c.Id equals st.Id
                       orderby c.Description
                       select new { c.Description, st.State }).ToList();
    return Page();
    
    I  have attempted to implement this solution, however, I am getting an error "CS1936 Could not find an implementation of the query pattern for source type City.  'Join' not found."

    public Cities List <City> { get; set; } = new List<City>();

    public States List<USStates> { get; set; } - new List<USStates>();

    Now somehow you are going to need to load each collection with the appropriate object, and then you can use Linq to query them.

    Monday, December 16, 2019 10:08 AM
  • User-1580299748 posted

    Hello DA924!

    Let me ask the question a different way.

    Suppose you have my two Classes (USSTATE and CITY).

    A STATE has a one to many relationship with CITY.  One STATE has many CITIES.

    The end result is that you want your Razor web page to display something like this (you want to display all rows in the CITY table, with its associated STATE).

    City State
    Cleveland Ohio
    Las Vegas  Nevada
    Austin Texas

    You are writing this code in the OnGet() Handler of your Index.cshtml file.

    How would you write your OnGet() handler?

    Thanks!  I sincerely appreciate your time.

    Monday, December 16, 2019 8:47 PM
  • User475983607 posted

    Your original code is joining the state ID to a City ID.   Use the state ID to state ID.

    Monday, December 16, 2019 9:44 PM
  • User1120430333 posted

    The StateID in City shold be joined on ID in the USState. 

    var StateAndCity = (from st in USState 
                        join c in City
                        on c.StateID Id equals st.Id
                        orderby c.Description
                        select new { c.Description, st.State }).ToList();

    If seems to me that City should have a collection within State.

    Also,  I would do this at the database level not up in memory the way you have it.  If you pulled cities for each state trying to hold it all in memory would not be an optimal approach, IMO.

    What's the purpose of what you're trying to do?

    Monday, December 16, 2019 11:25 PM
  • User-1580299748 posted

    Thanks for the reply!

    I updated the code to this:

    public City City { get; set; }
    public USStates State { get; set; }

    public IActionResult OnGet()
    {
    var StateAndCity = (from c in City
    join st in State
    on c.StateId equals st.Id
    orderby c.Description
    select new { c.Description, st.State }).ToList();

    return Page();

    Getting the following error:

    Error CS1936 Could not find an implementation of the query pattern for source type 'City'. 'Join' not found. 

    Monday, December 16, 2019 11:26 PM
  • User-474980206 posted

    a .net core best practice is to read the documentation.  linq works with collections: 

    var cities = new List<City> = {new City{...}, ...};      // create list of cities
    var states = new List<State> = {new State{...}...}}    // create list of states

    then you can query the collections:

    var StateAndCity = (from c in cities
       join st in states
          on c.StateId equals st.Id
       orderby c.Description
       select new { c.Description, st.State }).ToList();

    but you question implies you want to query a database. This is different. You must use EF core for this. You create a dbcontext and define the schema in the context. Then you can query the context. assume you defined a dbcontext for your database, and created an instance named db. then its: 

    var StateAndCity = (from c in db.Cities
       join st in db.States
          on c.StateId equals st.Id
       orderby c.Description
       select new { c.Description, st.State }).ToList();
    

    lastly you are returning an anonymous object, so the type will not be defined outside the controller method. This is a bad practice.  
     

    Monday, December 16, 2019 11:48 PM
  • User1120430333 posted

    It's still confusing as to how City is being derived and loaded. The example code you are showing the query is against no database controlled by EF. Is City supposed to be in a collection held in memory? If City objects were in a Cities collection, then you can query Cities with Linq, which is many City objects in the Cities collection. And if States were a collection too, then you can do a Join. But you just can't query City by itself a single object held in memory that is standalone, unless the single object was in the Cities collection by itself. You can't enumerate over a single object, a standalone single object. You can't loop over a single object in memory. You can loop over objects in an array or collection becuase they are enumerable. 

    Linq is a language that expects data objects to be in a tubular format that is enumerable, becuase that is what Linq does, and  it accesses objects in a collection, array or datatable by enumeration. A tubular format is an array, collection, datatable etc. and ect, which are objects that can be enumerated over.

    https://www.codeproject.com/Articles/1236958/LINQ-Part-A-Deep-Dive-into-IEnumerable

    I think your big problem here is that you are using EF Code first,  and you don't know how to build the Model when you should be using a Model using EF that builds the Model from an existing database. The database is built  by you using MS SQL Server Express and MS SQL Server Management Stuido where SSMS will guide you in making the tables and relationships. Then EF will create the EF Model correctly from an existing database. That's how I do it, becuase SSMS will catch you if you go wrong and will stop you if doing something erroneously in making tables and relationships. 

    https://www.entityframeworktutorial.net/efcore/create-model-for-existing-database-in-ef-core.aspx

    Error CS1936 Could not find an implementation of the query pattern for source type 'City'. 'Join' not found.

    You can't do what you are trying to do in your Linq query, becuase City is not a collection of City objects in a collection object that can be enumerated over. You can't Join on a single object that is standalone with a collection object. There has to be two or more collections involved in the join, like the collection being joined with another collection or collections.

    Tuesday, December 17, 2019 4:22 AM
  • User711641945 posted

    Hi bsashcraft,

    Here is a working sample like below:

    1.Model:

    public class USState
    {
        [Key]
        public int Id { get; set; }
    
        [Required]
        public string State { get; set; }
    
        [Required]
        public string Abbreviation { get; set; }
    }
    
    public class City
    {
        [Key]
        public int Id { get; set; }
    
        [Required]
        [Display(Name = "City")]
        public string Description { get; set; }
    
        public int StateID { get; set; }
        [ForeignKey("StateID")]
        public virtual USState USState { get; set; }
    }

    2.Index.cshtml:

    @page
    @model IndexModel
    
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(model => model.City[0].Description)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.City[0].USState.State)
                </th>
            </tr>
        </thead>
        <tbody>
    @foreach (var item in Model.City) {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Description)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.USState.State)
                </td>           
            </tr>
    }
        </tbody>
    </table>

    3.Index.cshtml.cs:

    public class IndexModel : PageModel
    {
        private readonly RazorProjContext _context;
    
        public IndexModel(RazorProjContext context)
        {
            _context = context;
        }
        public IList<City> City { get;set; }
    
        public async Task OnGetAsync()
        {
            City = await _context.City.Include(c=>c.USState).ToListAsync();
        }
    }

    4.DbContext:

    public class RazorProjContext : DbContext
    {
        public RazorProjContext (DbContextOptions<RazorProjContext> options)
            : base(options)
        {
        }
    
        public DbSet<USState> USState { get; set; }
        public DbSet<City> City { get; set; }
    }

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 17, 2019 9:49 AM
  • User1120430333 posted

    @Rena NI

    You do realize the amount of data that would be loaded up into collections of City and State within City held in memory for all 50 states, right? :)

    Tuesday, December 17, 2019 2:09 PM
  • User-1580299748 posted

    Thank you!  Thank you !  Thank you!!!!

    Tuesday, December 17, 2019 6:14 PM
  • User179968811 posted

    Thank you so much.. It was really helpful..Thanks a lot 

    Wednesday, December 18, 2019 6:19 AM