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

Reply

bsashcraft

Member

17 Points

39 Posts

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

Dec 16, 2019 07:45 AM|bsashcraft|LINK

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!

Reply

DA924

Contributor

4213 Points

3567 Posts

Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table

Dec 16, 2019 10:08 AM|DA924|LINK

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.

If you find the post has answered your issue, then please mark post as 'answered'.

Reply

bsashcraft

Member

17 Points

39 Posts

Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table

Dec 16, 2019 08:47 PM|bsashcraft|LINK

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.

Reply

mgebhard

All-Star

45051 Points

19447 Posts

Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table

Dec 16, 2019 09:44 PM|mgebhard|LINK

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

Reply

DA924

Contributor

4213 Points

3567 Posts

Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table

Dec 16, 2019 11:25 PM|DA924|LINK

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?

If you find the post has answered your issue, then please mark post as 'answered'.

Reply

bsashcraft

Member

17 Points

39 Posts

Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table

Dec 16, 2019 11:26 PM|bsashcraft|LINK

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.

Reply

bruce (sqlwo...

All-Star

54284 Points

13655 Posts

Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table

Dec 16, 2019 11:48 PM|bruce (sqlwork.com)|LINK

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.

bruce (sqlwork.com)

Reply

DA924

Contributor

4213 Points

3567 Posts

Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table

Dec 17, 2019 04:22 AM|DA924|LINK

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.

If you find the post has answered your issue, then please mark post as 'answered'.

Reply

Rena Ni

Participant

1420 Points

475 Posts

Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table

Dec 17, 2019 09:49 AM|Rena Ni|LINK

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

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.