I get that a view is custom option for Microsoft SQL server but the EF Query Type (vs. Entity Type) is not designed specifically for views, it also supports queries from tables with no need for a unique entity id.
Here's a summary: <a class="postlink" href="https://docs.microsoft.com/en-us/ef/core/modeling/query-types">https://docs.microsoft.com/en-us/ef/cor ... uery-types</a>
Compare query types to entity types Query types are like entity types in that they:
Can be added to the model either in OnModelCreating or via a "set" property on a derived DbContext. Support many of the same mapping capabilities, like inheritance mapping and navigation properties. On relational stores, they can configure the target database objects and columns via fluent API methods or data annotations. However, they are different from entity types in that they:
Do not require a key to be defined. Are never tracked for changes on the DbContext and therefore are never inserted, updated or deleted on the database. Are never discovered by convention. Only support a subset of navigation mapping capabilities - Specifically: They may never act as the principal end of a relationship. They can only contain reference navigation properties pointing to entities. Entities cannot contain navigation properties to query types. Are addressed on the ModelBuilder using the Query method rather than the Entity method. Are mapped on the DbContext through properties of type DbQuery<T> rather than DbSet<T> Are mapped to database objects using the ToView method, rather than ToTable. May be mapped to a defining query - A defining query is a secondary query declared in the model that acts a data source for a query type. Usage scenarios
Some of the main usage scenarios for query types are:
Serving as the return type for ad hoc FromSql() queries. Mapping to database views. Mapping to tables that do not have a primary key defined. Mapping to queries defined in the model.
Mapping to database objects Mapping a query type to a database object is achieved using the ToView fluent API. From the perspective of EF Core, the database object specified in this method is a view, meaning that it is treated as a read-only query source and cannot be the target of update, insert or delete operations. <ins>However, this does not mean that the database object is actually required to be a database view - It can alternatively be a database table that will be treated as read-only</ins>. Conversely, for entity types, EF Core assumes that a database object specified in the ToTable method can be treated as a table, meaning that it can be used as a query source but also targeted by update, delete and insert operations. In fact, you can specify the name of a database view in ToTable and everything should work fine as long as the view is configured to be updatable on the database.
So, I think EF Query Type is an EF feature and not specific to SQL server and seems pretty wide-spectrum to me.
Jamie
Thanks, I've got it working through a custom repository.
I was wishfully thinking that I might be able to use a DbQuery in my DbContext and the Fluent API toView method to map my query type to a view.
After a bit more digging, it looks like support would need to be added to the ABP & ASP.NET Zero frameworks. I'm not sure exactly how that would look but perhaps a new repository interface or support added for the existing IRepository.
I think this would greatly simplify the use of views, which could also then be incorporated into EF Core & LINQ queries and allow the results to be mapped to domain or view classes.
Hi Aaron
Thank you for your response.
If I use DbSet, EF core will try and generate a table when I add the next migration. Since I am trying to query a view, I want to avoid that.
It looks like EF Core 2.1 supports DbQuery for Views along with the model builder .Query<>().ToView(). Do you know if there is support for these techniques with ASP.NET Zero or ABP ? Or plans to add support?
Or, do I need to use a custom repository to query from a view? Or is there another recommended approach - like manually registering the view model to be injected into the application service?
Thanks Jamie
Hello, I recently downloaded a new clean version of the application:
Company Name: Blank Project Name: CTS Project Type: ASP.NET CORE & Angular Project Version: 5.3.0 One Solution: false Framework: .Net Core 2.0
Without any changes to the code I loaded this in Visual Studio Community For Mac (Version 7.4.3 build 10). This is running on macOS High Sierra Version 10.13.4.
Ran the Migrator to initialize the database then ran the CTS.Web.Host project.
Navigated to the swagger interface (loaded when application started) and went down to the GET /api/services/app/Timing/GetTimezones section. Tried it out with a DefaultTimezoneScope of 1.
Result was a 500 error "Error: Internal Server Error"
INFO 2018-04-18 09:40:06,507 [25 ] soft.AspNetCore.Hosting.Internal.WebHost - Request starting HTTP/1.1 GET http://localhost:5000/api/services/app/Timing/GetTimezones?DefaultTimezoneScope=1
INFO 2018-04-18 09:40:06,507 [25 ] uthentication.JwtBearer.JwtBearerHandler - Bearer was not authenticated. Failure message: No SecurityTokenValidator available for token: null
INFO 2018-04-18 09:40:06,751 [25 ] ore.Mvc.Internal.ControllerActionInvoker - Executing action method CTS.Timing.TimingAppService.GetTimezones (CTS.Application) with arguments (CTS.Timing.Dto.GetTimezonesInput) - ModelState is Valid
ERROR 2018-04-18 09:40:08,255 [12 ] Mvc.ExceptionHandling.AbpExceptionFilter - Exception of type 'System.TimeZoneNotFoundException' was thrown.
System.TimeZoneNotFoundException: Exception of type 'System.TimeZoneNotFoundException' was thrown.
at TimeZoneConverter.TZConvert.GetTimeZoneInfo(String windowsOrIanaTimeZoneId)
at CTS.Timing.TimeZoneService.FindTimeZoneById(String timezoneId) in /Users/steven/Downloads/CTS/aspnet-core/src/CTS.Core/Timing/TimeZoneService.cs:line 55
at CTS.Timing.TimingAppService.<GetTimezoneInfos>d__4.MoveNext() in /Users/steven/Downloads/CTS/aspnet-core/src/CTS.Application/Timing/TimingAppService.cs:line 45
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at CTS.Timing.TimingAppService.<GetTimezones>d__2.MoveNext() in /Users/steven/Downloads/CTS/aspnet-core/src/CTS.Application/Timing/TimingAppService.cs:line 21
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at lambda_method(Closure , Object )
at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeNextExceptionFilterAsync>d__23.MoveNext()
INFO 2018-04-18 09:40:08,878 [12 ] etCore.Mvc.Internal.ObjectResultExecutor - Executing ObjectResult, writing value Microsoft.AspNetCore.Mvc.ControllerContext.
INFO 2018-04-18 09:40:08,892 [12 ] ore.Mvc.Internal.ControllerActionInvoker - Executed action CTS.Timing.TimingAppService.GetTimezones (CTS.Application) in 2352.173ms
INFO 2018-04-18 09:40:08,893 [12 ] soft.AspNetCore.Hosting.Internal.WebHost - Request finished in 2385.623ms 500 application/json; charset=utf-8
As you can see we are not getting the right time zone information.
I tried to upload the project but your forum would not let me, it does not give an error just reloads the page and erases my post.
@ismcagdas
I have gone through all suggestions and still have the same issue, it looks like others are having the same issue as well.
The list of timezones coming from the api is still duplicating the display text but has Iana values for the value.
This is what comes back from the /api/services/app/Timing/GetTimezones call:
{
"result": {
"items": [
{
"name": "Default [UTC]",
"value": ""
},
{
"name": "Hawaii-Aleutian Standard Time",
"value": "America/Adak"
},
{
"name": "Hawaii-Aleutian Standard Time",
"value": "Pacific/Honolulu"
},
{
"name": "Marquesas Time",
"value": "Pacific/Marquesas"
},
{
"name": "Alaska Standard Time",
"value": "America/Anchorage"
},
{
"name": "Pacific Standard Time",
"value": "America/Tijuana"
},
{
"name": "Pacific Standard Time",
"value": "America/Los_Angeles"
},
...
Selecting the second "Pacific Standard Time" entry puts the value "America/Los_Angeles" into the database, then when the page refreshes it errors:
ERROR 2018-04-17 16:29:45,333 [3 ] Mvc.ExceptionHandling.AbpExceptionFilter - Unable to map America/Los_Angeles to iana timezone.
System.Exception: Unable to map America/Los_Angeles to iana timezone.
at Abp.Timing.Timezone.TimezoneHelper.WindowsToIana(String windowsTimezoneId) in D:\Github\aspnetboilerplate\src\Abp\Timing\Timezone\TimezoneHelper.cs:line 45
at Abp.Web.Configuration.AbpUserConfigurationBuilder.<GetUserTimingConfig>d__46.MoveNext() in D:\Github\aspnetboilerplate\src\Abp.Web.Common\Web\Configuration\AbpUserConfigurationBuilder.cs:line 241
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Abp.Web.Configuration.AbpUserConfigurationBuilder.<GetAll>d__37.MoveNext() in D:\Github\aspnetboilerplate\src\Abp.Web.Common\Web\Configuration\AbpUserConfigurationBuilder.cs:line 66
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Abp.AspNetCore.Mvc.Controllers.AbpUserConfigurationController.<GetAll>d__2.MoveNext() in D:\Github\aspnetboilerplate\src\Abp.AspNetCore\AspNetCore\Mvc\Controllers\AbpUserConfigurationController.cs:line 18
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at lambda_method(Closure , Object )
at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeNextExceptionFilterAsync>d__23.MoveNext()
As you can see it tries to convert the Iana timezone to a windows timezone.
@ismcagdas I don't think your understanding the situation.
We are running the ASP.Net Core version 2.0 under a Linux docker container, if we use a Windows Timezone ID it will fail the look up because the system does not have Windows Timezones. The initial lookup TimezoneHelper.FindTimeZoneInfo(timezoneId); is what fails because passing a Windows Timezone Id fails the system lookup. We have tried Windows Timezone Ids and Iana Timezone Ids and they both fail when running on a Linux machine.
This works fine in Windows.
Hello, thanks @aaron for the details on getting access to Github, we have looked at the issue posted by @ismcagdas <a class="postlink" href="https://github.com/aspnetzero/aspnet-zero-core/issues/667">https://github.com/aspnetzero/aspnet-ze ... issues/667</a> however this is not the issue we are having.
We are running on a docker image (linux) and the list of timezones that come back from the /api/services/app/Timing/GetTimezones call is listing the Iana values instead of the WindowsId values:
{
"result": {
"items": [
{
"name": "Default [UTC]",
"value": ""
},
{
"name": "Hawaii-Aleutian Standard Time",
"value": "America/Adak"
},
{
"name": "Hawaii-Aleutian Standard Time",
"value": "Pacific/Honolulu"
},
{
"name": "Marquesas Time",
"value": "Pacific/Marquesas"
},
{
"name": "Alaska Standard Time",
"value": "America/Anchorage"
},
{
"name": "Pacific Standard Time",
"value": "America/Tijuana"
},
{
"name": "Pacific Standard Time",
"value": "America/Los_Angeles"
},
{
"name": "Mexican Pacific Standard Time",
"value": "America/Chihuahua"
},
{
"name": "Mountain Standard Time",
"value": "America/Denver"
},
{
"name": "Mountain Standard Time",
"value": "America/Phoenix"
},
{
"name": "Central Standard Time",
"value": "America/Regina"
},
{
"name": "Central Standard Time",
"value": "America/Guatemala"
},
{
"name": "Central Standard Time",
"value": "America/Mexico_City"
},
{
"name": "Central Standard Time",
"value": "America/Chicago"
},
...
As you can see we get a lot of duplicate name with different values (the user sees this as duplicate entries in the combobox)
Once we select one of the values in that list (say Central Standard Time with a value of America/Chicago) the application fails to load because it is trying to convert the value America/Chicago from Windows to Iana. See code at: <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate/blob/v3.5.0/src/Abp.Web.Common/Web/Configuration/AbpUserConfigurationBuilder.cs#L254">https://github.com/aspnetboilerplate/as ... er.cs#L254</a>
If we change the database to a WindowsId instead (say "Central Standard Time") we get another failure because it tries to load the Timezone info with the TimezoneHelper.FindTimeZoneInfo(timezoneId); call but the system is not Windows and so it fails. See code at: <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate/blob/v3.5.0/src/Abp.Web.Common/Web/Configuration/AbpUserConfigurationBuilder.cs#L239">https://github.com/aspnetboilerplate/as ... er.cs#L239</a>
Hi
Are you able to provide access to the link in GitHub?
Our user name is AlderCove.
Thanks Jamie
This appears to be a related issue: #4112@fadf58ad-0efa-4ad0-ae87-d51c59310008
After looking at the Abp code in relation to the error message I can see that the problem is at this location:
<a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate/blob/v3.5.0/src/Abp.Web.Common/Web/Configuration/AbpUserConfigurationBuilder.cs#L254">https://github.com/aspnetboilerplate/as ... er.cs#L254</a>
protected virtual async Task<AbpUserTimingConfigDto> GetUserTimingConfig()
{
var timezoneId = await SettingManager.GetSettingValueAsync(TimingSettingNames.TimeZone);
var timezone = TimezoneHelper.FindTimeZoneInfo(timezoneId);
return new AbpUserTimingConfigDto
{
TimeZoneInfo = new AbpUserTimeZoneConfigDto
{
Windows = new AbpUserWindowsTimeZoneConfigDto
{
TimeZoneId = timezoneId,
BaseUtcOffsetInMilliseconds = timezone.BaseUtcOffset.TotalMilliseconds,
CurrentUtcOffsetInMilliseconds = timezone.GetUtcOffset(Clock.Now).TotalMilliseconds,
IsDaylightSavingTimeNow = timezone.IsDaylightSavingTime(Clock.Now)
},
Iana = new AbpUserIanaTimeZoneConfigDto
{
TimeZoneId = TimezoneHelper.WindowsToIana(timezoneId)
}
}
};
}
Specifically at TimeZoneId = TimezoneHelper.WindowsToIana(timezoneId), here the timezoneId is already an Iana timezone not a windows time zone on my system (macOS).