An ASP.NET Core OData Example using the Wide WorldImporters database and Entity Framework Core #674

bytefish · 2022-08-25T08:20:54Z

bytefish
Aug 25, 2022

I have written an article on ASP.NET Core OData 8, that uses the WideWorldImporters database and uses EntityFramework Core for scaffolding the database:

https://www.bytefish.de/blog/aspnet_core_odata_example.html

It also shows how to generate the database model, ODataController, Swagger documentation and what’s necessary to support Spatial queries. I think there is a lot of potential in it for interesting queries.

It would be great to get some feedback on it!

bkwdesign · 2024-10-24T19:22:48Z

bkwdesign
Oct 24, 2024

I am a little rough with my Docker skills, but, all three images built successfully and came online.
When the front-end application tries to render OData from the web api, I see this error message:

Login failed for user 'wwi'. Reason: Could not find a login matching the name provided. [CLIENT: 172.18.0.2]

2024-10-24 15:11:47 wwi-api               | [19:11:47 INF] Request starting HTTP/2 GET https://localhost:5000/odata/ColdRoomTemperatures?%24count=true&%24skip=0&%24top=10 - null null
2024-10-24 15:11:47 wwi-api               | [19:11:47 INF] CORS policy execution successful.
2024-10-24 15:11:47 wwi-api               | [19:11:47 INF] Executing endpoint 'WideWorldImporters.Api.Controllers.EntitiesController.GetColdRoomTemperatures (WideWorldImporters.Server.Api)'
2024-10-24 15:11:47 wwi-api               | [19:11:47 INF] Route matched with {action = "GetColdRoomTemperatures", controller = "Entities"}. Executing controller action with signature Microsoft.AspNetCore.Mvc.IActionResult GetColdRoomTemperatures() on controller WideWorldImporters.Api.Controllers.EntitiesController (WideWorldImporters.Server.Api).
2024-10-24 15:11:48 wwi-api               | warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
2024-10-24 15:11:48 wwi-api               |       Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
2024-10-24 15:11:48 wwi-api               | [19:11:48 WRN] Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
2024-10-24 15:11:49 wwi-api               | [19:11:49 INF] Executing OkObjectResult, writing value of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[[WideWorldImporters.Server.Database.Models.ColdRoomTemperature, WideWorldImporters.Server.Database, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'.
2024-10-24 15:11:49 wwi-db-sqlserver2022  | 
2024-10-24 19:11:49.47 Logon       Error: 18456, Severity: 14, State: 5.
2024-10-24 15:11:49 wwi-db-sqlserver2022  | 
2024-10-24 19:11:49.47 Logon       Login failed for user 'wwi'. Reason: Could not find a login matching the name provided. [CLIENT: 172.18.0.2]

Because I'm on a windows box, I ran this command (after I built the images)

dotnet dev-certs https -ep %USERPROFILE%/.aspnet/https/aspnetapp.pfx -p SuperStrongPassword --trust

2 replies

bkwdesign Oct 25, 2024

OK, your stuff is finally working on my machine!

Shame on me for not generating the aspnetapp.pfx first!

I generated bad images and it took me a good bit to figure out where the bad stuff was cached.

bytefish Oct 25, 2024
Author

Great! Glad to hear it works. 👍🏻

bkwdesign · 2024-10-25T14:22:25Z

bkwdesign
Oct 25, 2024

Ok, after watching the logs, this confirms that my personal experiences in OData v9 libraries are not any different than what you've built. I toyed with your wwi-app so that it will submit specific fields in a $select query option, but, I'm noticing that the [EnableQuery] Web API method does not seem to translate those into the generated T-SQL statements.

Specifically, I wanted the customer grid / view model to only request the displayed fields: CustomerId,CustomerName,AccountOpenedDate,IsOnCreditHold

See the log for full details:

wwi-api               | [14:17:26 INF] Request starting HTTP/2 GET https://localhost:5000/odata/Customers?%24count=true&%24expand=LastEditedByNavigation&%24select=CustomerId,CustomerName,AccountOpenedDate,IsOnCreditHold&%24skip=10&%24top=10 - null null
wwi-api               | [14:17:26 INF] CORS policy execution successful.
wwi-api               | [14:17:26 INF] Executing endpoint 'WideWorldImporters.Api.Controllers.EntitiesController.GetCustomers (WideWorldImporters.Server.Api)'
wwi-api               | [14:17:26 INF] Route matched with {action = "GetCustomers", controller = "Entities"}. Executing controller action with signature Microsoft.AspNetCore.Mvc.IActionResult GetCustomers() on controller WideWorldImporters.Api.Controllers.EntitiesController (WideWorldImporters.Server.Api).
wwi-api               | [14:17:27 INF] Executing OkObjectResult, writing value of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[[Microsoft.AspNetCore.OData.Query.Wrapper.SelectSome`1[[WideWorldImporters.Server.Database.Models.Customer, WideWorldImporters.Server.Database, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], Microsoft.AspNetCore.OData, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]'.
wwi-api               | info: Microsoft.EntityFrameworkCore.Database.Command[20101]
wwi-api               |       Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
wwi-api               |       SELECT COUNT_BIG(*)
wwi-api               |       FROM [Sales].[Customers] AS [c]
wwi-api               | [14:17:27 INF] Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
wwi-api               | SELECT COUNT_BIG(*)
wwi-api               | FROM [Sales].[Customers] AS [c]
wwi-api               | info: Microsoft.EntityFrameworkCore.Database.Command[20101]
wwi-api               |       Executed DbCommand (1ms) [Parameters=[@__TypedProperty_2='10', @__TypedProperty_3='10'], CommandType='Text', CommandTimeout='30']
wwi-api               |       SELECT [p].[PersonID], [p].[CustomFields], [p].[EmailAddress], [p].[FaxNumber], [p].[FullName], [p].[HashedPassword], [p].[IsEmployee], [p].[IsExternalLogonProvider], [p].[IsPermittedToLogon], [p].[IsSalesperson], [p].[IsSystemUser], [p].[LastEditedBy], [p].[LogonName], [p].[OtherLanguages], [p].[PhoneNumber], [p].[Photo], [p].[PreferredName], [p].[SearchName], [p].[UserPreferences], [p].[ValidFrom], [p].[ValidTo], [t].[CustomerID], [t].[CustomerName], [t].[AccountOpenedDate], [t].[IsOnCreditHold], CAST(0 AS bit)
wwi-api               |       FROM (
wwi-api               |           SELECT [c].[CustomerID], [c].[AccountOpenedDate], [c].[CustomerName], [c].[IsOnCreditHold], [c].[LastEditedBy]
wwi-api               |           FROM [Sales].[Customers] AS [c]
wwi-api               |           ORDER BY [c].[CustomerID]
wwi-api               |           OFFSET @__TypedProperty_2 ROWS FETCH NEXT @__TypedProperty_3 ROWS ONLY
wwi-api               |       ) AS [t]
wwi-api               |       INNER JOIN [Application].[People] AS [p] ON [t].[LastEditedBy] = [p].[PersonID]
wwi-api               |       ORDER BY [t].[CustomerID]
wwi-api               | [14:17:27 INF] Executed DbCommand (1ms) [Parameters=[@__TypedProperty_2='10', @__TypedProperty_3='10'], CommandType='Text', CommandTimeout='30']
wwi-api               | SELECT [p].[PersonID], [p].[CustomFields], [p].[EmailAddress], [p].[FaxNumber], [p].[FullName], [p].[HashedPassword], [p].[IsEmployee], [p].[IsExternalLogonProvider], [p].[IsPermittedToLogon], [p].[IsSalesperson], [p].[IsSystemUser], [p].[LastEditedBy], [p].[LogonName], [p].[OtherLanguages], [p].[PhoneNumber], [p].[Photo], [p].[PreferredName], [p].[SearchName], [p].[UserPreferences], [p].[ValidFrom], [p].[ValidTo], [t].[CustomerID], [t].[CustomerName], [t].[AccountOpenedDate], [t].[IsOnCreditHold], CAST(0 AS bit)
wwi-api               | FROM (
wwi-api               |     SELECT [c].[CustomerID], [c].[AccountOpenedDate], [c].[CustomerName], [c].[IsOnCreditHold], [c].[LastEditedBy]
wwi-api               |     FROM [Sales].[Customers] AS [c]
wwi-api               |     ORDER BY [c].[CustomerID]
wwi-api               |     OFFSET @__TypedProperty_2 ROWS FETCH NEXT @__TypedProperty_3 ROWS ONLY
wwi-api               | ) AS [t]
wwi-api               | INNER JOIN [Application].[People] AS [p] ON [t].[LastEditedBy] = [p].[PersonID]
wwi-api               | ORDER BY [t].[CustomerID]
wwi-api               | [14:17:27 INF] Executed action WideWorldImporters.Api.Controllers.EntitiesController.GetCustomers (WideWorldImporters.Server.Api) in 55.8302ms
wwi-api               | [14:17:27 INF] Executed endpoint 'WideWorldImporters.Api.Controllers.EntitiesController.GetCustomers (WideWorldImporters.Server.Api)'
wwi-api               | [14:17:27 INF] Request finished HTTP/2 GET https://localhost:5000/odata/Customers?%24count=true&%24expand=LastEditedByNavigation&%24select=CustomerId,CustomerName,AccountOpenedDate,IsOnCreditHold&%24skip=10&%24top=10 - 200 null application/json; odata.metadata=minimal; odata.streaming=true; charset=utf-8 58.4226ms

=====
EDIT

I misspoke.. I can see in the inner query that the query options are being interpreted correctly...

SELECT [c].[CustomerID], [c].[AccountOpenedDate], [c].[CustomerName], [c].[IsOnCreditHold], [c].[LastEditedBy]
  FROM [Sales].[Customers] AS [c]
  ORDER BY [c].[CustomerID]

0 replies

bkwdesign · 2024-10-25T14:27:51Z

bkwdesign
Oct 25, 2024

I'm reviewing my prior comment and think I may have misspoke. I am seeing that there is an inner query that does indeed seem to match the $select field options.

I am posting the original logs that show how things looked before I made the edit to the wwi-app

2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Request starting HTTP/2 GET https://localhost:5000/odata/Customers?%24count=true&%24expand=LastEditedByNavigation&%24skip=90&%24top=10 - null null
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] CORS policy execution successful.
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Executing endpoint 'WideWorldImporters.Api.Controllers.EntitiesController.GetCustomers (WideWorldImporters.Server.Api)'
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Route matched with {action = "GetCustomers", controller = "Entities"}. Executing controller action with signature Microsoft.AspNetCore.Mvc.IActionResult GetCustomers() on controller WideWorldImporters.Api.Controllers.EntitiesController (WideWorldImporters.Server.Api).
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Executing OkObjectResult, writing value of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[[Microsoft.AspNetCore.OData.Query.Wrapper.SelectAllAndExpand`1[[WideWorldImporters.Server.Database.Models.Customer, WideWorldImporters.Server.Database, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], Microsoft.AspNetCore.OData, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]'.
2024-10-25 09:53:58 wwi-api               | info: Microsoft.EntityFrameworkCore.Database.Command[20101]
2024-10-25 09:53:58 wwi-api               |       Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
2024-10-25 09:53:58 wwi-api               |       SELECT COUNT_BIG(*)
2024-10-25 09:53:58 wwi-api               |       FROM [Sales].[Customers] AS [c]
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
2024-10-25 09:53:58 wwi-api               | SELECT COUNT_BIG(*)
2024-10-25 09:53:58 wwi-api               | FROM [Sales].[Customers] AS [c]
2024-10-25 09:53:58 wwi-api               | info: Microsoft.EntityFrameworkCore.Database.Command[20101]
2024-10-25 09:53:58 wwi-api               |       Executed DbCommand (1ms) [Parameters=[@__TypedProperty_2='90', @__TypedProperty_3='10'], CommandType='Text', CommandTimeout='30']
2024-10-25 09:53:58 wwi-api               |       SELECT [t].[CustomerID], [t].[AccountOpenedDate], [t].[AlternateContactPersonID], [t].[BillToCustomerID], [t].[BuyingGroupID], [t].[CreditLimit], [t].[CustomerCategoryID], [t].[CustomerName], [t].[DeliveryAddressLine1], [t].[DeliveryAddressLine2], [t].[DeliveryCityID], [t].[DeliveryLocation], [t].[DeliveryMethodID], [t].[DeliveryPostalCode], [t].[DeliveryRun], [t].[FaxNumber], [t].[IsOnCreditHold], [t].[IsStatementSent], [t].[LastEditedBy], [t].[PaymentDays], [t].[PhoneNumber], [t].[PostalAddressLine1], [t].[PostalAddressLine2], [t].[PostalCityID], [t].[PostalPostalCode], [t].[PrimaryContactPersonID], [t].[RunPosition], [t].[StandardDiscountPercentage], [t].[ValidFrom], [t].[ValidTo], [t].[WebsiteURL], [p].[PersonID], [p].[CustomFields], [p].[EmailAddress], [p].[FaxNumber], [p].[FullName], [p].[HashedPassword], [p].[IsEmployee], [p].[IsExternalLogonProvider], [p].[IsPermittedToLogon], [p].[IsSalesperson], [p].[IsSystemUser], [p].[LastEditedBy], [p].[LogonName], [p].[OtherLanguages], [p].[PhoneNumber], [p].[Photo], [p].[PreferredName], [p].[SearchName], [p].[UserPreferences], [p].[ValidFrom], [p].[ValidTo], CAST(0 AS bit)
2024-10-25 09:53:58 wwi-api               |       FROM (
2024-10-25 09:53:58 wwi-api               |           SELECT [c].[CustomerID], [c].[AccountOpenedDate], [c].[AlternateContactPersonID], [c].[BillToCustomerID], [c].[BuyingGroupID], [c].[CreditLimit], [c].[CustomerCategoryID], [c].[CustomerName], [c].[DeliveryAddressLine1], [c].[DeliveryAddressLine2], [c].[DeliveryCityID], [c].[DeliveryLocation], [c].[DeliveryMethodID], [c].[DeliveryPostalCode], [c].[DeliveryRun], [c].[FaxNumber], [c].[IsOnCreditHold], [c].[IsStatementSent], [c].[LastEditedBy], [c].[PaymentDays], [c].[PhoneNumber], [c].[PostalAddressLine1], [c].[PostalAddressLine2], [c].[PostalCityID], [c].[PostalPostalCode], [c].[PrimaryContactPersonID], [c].[RunPosition], [c].[StandardDiscountPercentage], [c].[ValidFrom], [c].[ValidTo], [c].[WebsiteURL]
2024-10-25 09:53:58 wwi-api               |           FROM [Sales].[Customers] AS [c]
2024-10-25 09:53:58 wwi-api               |           ORDER BY [c].[CustomerID]
2024-10-25 09:53:58 wwi-api               |           OFFSET @__TypedProperty_2 ROWS FETCH NEXT @__TypedProperty_3 ROWS ONLY
2024-10-25 09:53:58 wwi-api               |       ) AS [t]
2024-10-25 09:53:58 wwi-api               |       INNER JOIN [Application].[People] AS [p] ON [t].[LastEditedBy] = [p].[PersonID]
2024-10-25 09:53:58 wwi-api               |       ORDER BY [t].[CustomerID]
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Executed DbCommand (1ms) [Parameters=[@__TypedProperty_2='90', @__TypedProperty_3='10'], CommandType='Text', CommandTimeout='30']
2024-10-25 09:53:58 wwi-api               | SELECT [t].[CustomerID], [t].[AccountOpenedDate], [t].[AlternateContactPersonID], [t].[BillToCustomerID], [t].[BuyingGroupID], [t].[CreditLimit], [t].[CustomerCategoryID], [t].[CustomerName], [t].[DeliveryAddressLine1], [t].[DeliveryAddressLine2], [t].[DeliveryCityID], [t].[DeliveryLocation], [t].[DeliveryMethodID], [t].[DeliveryPostalCode], [t].[DeliveryRun], [t].[FaxNumber], [t].[IsOnCreditHold], [t].[IsStatementSent], [t].[LastEditedBy], [t].[PaymentDays], [t].[PhoneNumber], [t].[PostalAddressLine1], [t].[PostalAddressLine2], [t].[PostalCityID], [t].[PostalPostalCode], [t].[PrimaryContactPersonID], [t].[RunPosition], [t].[StandardDiscountPercentage], [t].[ValidFrom], [t].[ValidTo], [t].[WebsiteURL], [p].[PersonID], [p].[CustomFields], [p].[EmailAddress], [p].[FaxNumber], [p].[FullName], [p].[HashedPassword], [p].[IsEmployee], [p].[IsExternalLogonProvider], [p].[IsPermittedToLogon], [p].[IsSalesperson], [p].[IsSystemUser], [p].[LastEditedBy], [p].[LogonName], [p].[OtherLanguages], [p].[PhoneNumber], [p].[Photo], [p].[PreferredName], [p].[SearchName], [p].[UserPreferences], [p].[ValidFrom], [p].[ValidTo], CAST(0 AS bit)
2024-10-25 09:53:58 wwi-api               | FROM (
2024-10-25 09:53:58 wwi-api               |     SELECT [c].[CustomerID], [c].[AccountOpenedDate], [c].[AlternateContactPersonID], [c].[BillToCustomerID], [c].[BuyingGroupID], [c].[CreditLimit], [c].[CustomerCategoryID], [c].[CustomerName], [c].[DeliveryAddressLine1], [c].[DeliveryAddressLine2], [c].[DeliveryCityID], [c].[DeliveryLocation], [c].[DeliveryMethodID], [c].[DeliveryPostalCode], [c].[DeliveryRun], [c].[FaxNumber], [c].[IsOnCreditHold], [c].[IsStatementSent], [c].[LastEditedBy], [c].[PaymentDays], [c].[PhoneNumber], [c].[PostalAddressLine1], [c].[PostalAddressLine2], [c].[PostalCityID], [c].[PostalPostalCode], [c].[PrimaryContactPersonID], [c].[RunPosition], [c].[StandardDiscountPercentage], [c].[ValidFrom], [c].[ValidTo], [c].[WebsiteURL]
2024-10-25 09:53:58 wwi-api               |     FROM [Sales].[Customers] AS [c]
2024-10-25 09:53:58 wwi-api               |     ORDER BY [c].[CustomerID]
2024-10-25 09:53:58 wwi-api               |     OFFSET @__TypedProperty_2 ROWS FETCH NEXT @__TypedProperty_3 ROWS ONLY
2024-10-25 09:53:58 wwi-api               | ) AS [t]
2024-10-25 09:53:58 wwi-api               | INNER JOIN [Application].[People] AS [p] ON [t].[LastEditedBy] = [p].[PersonID]
2024-10-25 09:53:58 wwi-api               | ORDER BY [t].[CustomerID]
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Executed action WideWorldImporters.Api.Controllers.EntitiesController.GetCustomers (WideWorldImporters.Server.Api) in 20.8139ms
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Executed endpoint 'WideWorldImporters.Api.Controllers.EntitiesController.GetCustomers (WideWorldImporters.Server.Api)'
2024-10-25 09:53:58 wwi-api               | [13:53:58 INF] Request finished HTTP/2 GET https://localhost:5000/odata/Customers?%24count=true&%24expand=LastEditedByNavigation&%24skip=90&%24top=10 - 200 null application/json; odata.metadata=minimal; odata.streaming=true; charset=utf-8 22.8817ms

0 replies

bkwdesign · 2024-10-25T14:34:46Z

bkwdesign
Oct 25, 2024

Many thanks @bytefish for this great application design; I installed docker long ago on my PC and had figured out the WSL2 business on my Windows 11 machine.. but this was the first time I really dusted it all off and used docker compose and got the full benefit of all the logs dynamically displayed "in my face".. this is a sweet setup.

QUESTION:
Did you use Visual Studio's tooling to create this overall composed environment or it is all hand-made?

7 replies

bkwdesign Oct 25, 2024

TBH - I've never used Visual Studio's containerization options and do not know if they even compare or not. I'm kinda swiss army knife guy at my place and sometimes I'm neck-deep in Salesforce or SAP Business One.. but this week I'm revisiting all-things-OData, as I think it's a great stack

bkwdesign Oct 25, 2024

@bytefish - after comparing your OData API to my own, we used near identical NuGet packages.. down to the version. However my API absoletely refuses to translate the $select options into the T-SQL generated by EF. I have borrowed bits and pieces from your EntityController.cs, your Program.cs.. even pasted over my .csproj's <PackageReferences> to make mine match yours.. and everything I can think of, but cannot resolve the problem in my stack - but your code sample gives me hope that it indeed can be resolved. My EF project makes use of some ValueTranslations and other things to support enum properties.. that's the only difference I can think of at the moment.

bytefish Oct 26, 2024
Author

This is weird. If it’s the same ASP.NET Core OData Version and the same EF Core version, it should result in the same queries. Is it possible, that some of the properties in your data model are marked as required and EF Core or LINQ implicitly adds the properties to the $select? That’s the only difference I can think of, since in my code most properties are marked as nullable.

bkwdesign Nov 4, 2024

Some of my entities generate T-SQL correctly aligned with the OData Query Options (i.e. they SELECT what is in the $select ) but to your point, the main entity that is mis-behaving does indeed have tons of [Required] fields. But I would have assumed those come into play when doing a PATCH or a POST and generate a lot of field-level validation errors. Does [Required] also force the property to exist (i.e. be queried) during a GET operation?

bytefish Nov 4, 2024
Author

@bkwdesign Yes, I don’t think, that’s the issue. As far as I know ASP.NET OData generates an SelectExpandWrapper or something along the lines and doesn’t map directly to the entities. Maybe you manage to create a minimal sample and create an issue.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

An ASP.NET Core OData Example using the Wide WorldImporters database and Entity Framework Core #674

{{title}}

Replies: 4 comments 9 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

Select a reply

An ASP.NET Core OData Example using the Wide WorldImporters database and Entity Framework Core #674

Replies: 4 comments · 9 replies

bytefish Oct 25, 2024 Author

===== EDIT

bytefish Oct 26, 2024 Author

bytefish Nov 4, 2024 Author

Replies: 4 comments 9 replies

bytefish Oct 25, 2024
Author

=====
EDIT

bytefish Oct 26, 2024
Author

bytefish Nov 4, 2024
Author