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.
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.
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'.
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.
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'.
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.
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.
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.
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.
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'.
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; }
}
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
17 Points
41 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!
Contributor
4923 Points
4198 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 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.
Member
17 Points
41 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).
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.
All-Star
53001 Points
23587 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.
Contributor
4923 Points
4198 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.
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?
Member
17 Points
41 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.
All-Star
58144 Points
15646 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:
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:
lastly you are returning an anonymous object, so the type will not be defined outside the controller method. This is a bad practice.
Contributor
4923 Points
4198 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.
Contributor
2690 Points
874 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:
2.Index.cshtml:
3.Index.cshtml.cs:
4.DbContext:
Best Regards,
Rena
Contributor
4923 Points
4198 Posts
Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table
Dec 17, 2019 02:09 PM|DA924|LINK
@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? :)
Member
17 Points
41 Posts
Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table
Dec 17, 2019 06:14 PM|bsashcraft|LINK
Thank you! Thank you ! Thank you!!!!
None
0 Points
1 Post
Re: .Net Core Best Practices For Returning Data From Linked (Foreign Key) Table
Dec 18, 2019 06:19 AM|Charley20|LINK
Thank you so much.. It was really helpful..Thanks a lot