none
Data Points - EF Core 2.1 Query Types RRS feed

  • General discussion

  • Julie Lerman delves into the new EF Core 2.1 Query Type feature, which lets you more easily query a database without needing true entities with key properties to consume the results.

    Read this article in the July 2018 issue of MSDN Magazine

    Monday, July 2, 2018 7:56 PM
    Owner

All replies

  • Hi,

    I haven't messed about with the code yet so am not completely sure of all the possibilities query types have, but it looks like now I can map the results of a stored procedure into a POCO using the technique in the third capability demonstrated (Capturing the results of FromSql queries with non-entity types) - is that correct?

    If so, is it possible to use this technique to map in the results from a stored proc that returns multiple resultsets to their relevant POCOs (as we could do in non-core EF)?

    Thanks in advance!

    Monday, July 9, 2018 7:55 AM
  • Also found this change useful for the same reasons as above. Have a query though...

    How can I manage mapping the results of a stored proc without it having to return all the properties of the class it maps to?

    I have a general stored proc that can return different result sets in terms of the fields it includes, can I map that to a single, common query type or do I need a suitable, matching DBQuery for each unique set returned?

    I don't want to have to return all fields if I don't need them just to satisfy the requirements of the query mapping.

    Thursday, July 26, 2018 1:53 PM
  • Hi Lerman,

    While I use DbQuery, should the columns in the select query match exactly same as the model/entity?

    IEnumerable<UserModel> test =  _context.FewUserColumns.FromSql(@"select id, last_name, from user where user_id  = @userId", param).ToListAsync();

    Below is the UserModel.

    public class userModel
     {
            public int id {get;set;}
            public string first_name { get; set; }
            public string last_name { get; set; }
     }

    I am not fetching first_name in the above select statement. due to this i see the below error. Is that expected?

    An unhandled exception occurred while processing the request.
    InvalidOperationException: The required column 'first_name' was not present in the results of a 'FromSql' operation.



    Tuesday, July 31, 2018 10:14 AM
  • Hi danbo0001

    I haven't tried that but based on this github issue, the ability to use fromsql to capture multiple resultsets seems to be on a backlog so assumign that extends to query types: https://github.com/aspnet/EntityFrameworkCore/issues/8127

    If you happen to try it out and find different results, let us know!


    Julie Lerman, Author of Programming Entity Framework, MVP

    Saturday, August 11, 2018 7:07 PM
    Answerer
  • I really don't know how to solve that one without spending some time doing some research. Perhaps someone will come along who has already sorted this one out. Sorry!

    Julie Lerman, Author of Programming Entity Framework, MVP

    Saturday, August 11, 2018 7:09 PM
    Answerer
  • Hi Rajesh,

    Yes, unfortunately that is a constraint...EF Core expects all of those properties to be available. Hopefully that will get easier in some upcoming release.
    Julie


    Julie Lerman, Author of Programming Entity Framework, MVP

    Saturday, August 11, 2018 7:10 PM
    Answerer
  • Hi Rajesh,

    Yes, unfortunately that is a constraint...EF Core expects all of those properties to be available. Hopefully that will get easier in some upcoming release.
    Julie


    Julie Lerman, Author of Programming Entity Framework, MVP

    Thank you Lerman!
    Thursday, September 13, 2018 10:09 AM
  • I encountered an issue after changing a "fake" entity that was based on a view from DbSet<T> to DbQuery<T>.

    I was getting the following error:

    System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: The entity type 'FctPropertyOccupancy' cannot be added to the model because a query type with the same name already exists.

    The problem was due to the fact that I was setting global filters and in the method that sets the filter I had to check whether the enclosing type was a QueryType or not.

    var configureAccountMethod = GetType().GetTypeInfo()
                    .DeclaredMethods
                    .Single(m => m.Name == nameof(ConfigureAccountFilter));            
    
                var accountEntityTypes = modelBuilder.Model
                    .GetEntityTypes()
                    .Where(t => typeof(IAccount).IsAssignableFrom(t.ClrType));
    
                var args = new object[] { modelBuilder, false };
    
                foreach (var entityType in accountEntityTypes)            
                {
                    args[1] = entityType.IsQueryType;                
                    configureAccountMethod.MakeGenericMethod(entityType.ClrType).Invoke(this, args);         
                }

    private void ConfigureAccountFilter<TEntity>(ModelBuilder modelBuilder, bool isQueryType) where TEntity : class, IAccount
            {            
                if (!isQueryType)
                    modelBuilder.Entity<TEntity>().HasQueryFilter(e => e.AccountKey == _accountKey);
                else 
                    modelBuilder.Query<TEntity>().HasQueryFilter(e => e.AccountKey == _accountKey);
            }

    Figure this may help others :)


    gdonovan

    Wednesday, January 30, 2019 6:49 PM
  • Thanks a bunch for sharing the solution, gdonovan! :)

    Julie Lerman, Author of Programming Entity Framework, MVP

    Wednesday, January 30, 2019 7:11 PM
    Answerer