EF Core server evaluation of filter on stored procedure result - c#

EF Core supports executing stored procedures and mapping the results to an entity (either existing entities or entities created specifically for the stored procedure).
I've read many issues around this topic, such as https://github.com/aspnet/EntityFrameworkCore/issues/1862 https://github.com/aspnet/EntityFrameworkCore/issues/245.
I thought that #1862 would enable server evaluation for stored procedures that only perform a SELECT operation.
However, it seems that my code results in 2 calls to the database. Is there a way to execute the following code with a single call to the database?
var customIdsQuery = _dbContext.Set<CustomId>()
.FromSql($"dbo.GetCustomIDs {username}").AsNoTracking();
var eventsQuery = _dbContext.Event.Where(ev => ev.User.Username == username)
.Join(customIdsQuery, ev => rev.CustomId, cid=> cid.Id, (ev, cid) => ev).AsNoTracking();
var events = eventsQuery.ToList(); // 2 database calls
The model builder looks something like that:
modelBuilder.Entity<CustomId>(); // For the stored procedure
modelBuilder.Entity<Event>(entity => // Key is defined as attribute on the model
{
entity.HasOne(d => d.Meeting)
.WithMany(p => p.Event)
.HasForeignKey(d => d.MeetingId);
entity.HasOne(d => d.User)
.WithMany(p => p.Event)
.HasForeignKey(d => d.UserId);
});
Am I missing something obvious in order to be able to have the entire query evaluate on the SQL server? From the documentation (https://docs.microsoft.com/en-us/ef/core/querying/raw-sql):
SELECT statements passed to this method should generally be composable: If EF Core needs to evaluate additional query operators on the server (e.g. to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery
My stored procedure is in fact like a SELECT statement, is there a way to enforce it being recognized as such?
Thanks.

Related

Can you see NHibernate results mid-query during debugging in Visual Studio?

Having been comfortably using Entity Framework for many years, I've just been thrown in the deep end with a project that uses NHibernate and I'm really struggling. The documentation is sparse and unhelpful if you're working with it for the first time, and most tutorial and example sites are out of date - I understand it changed significantly in v3?
Normally, I learn things best when trying to work with them, so I jumped in and tried to see what I could do. But I've hit a bug in this pre-existing function (none of this code is mine):
public IDictionary<long, string> GetSeriesFilterData(string userId)
{
Series seriesAlias = null;
Event eventAlias = null;
Session sessionAlias = null;
Dealership dealershipAlias = null;
var query = _repository.Session.QueryOver(() => seriesAlias)
.Where(() => !seriesAlias.IsArchived);
var dealershipIds = QueryOver.Of<ApplicationUserDealership>()
.Where(x => x.ApplicationUser_Key == userId)
.SelectList(list => list.SelectGroup(x => x.Dealership_Id));
dealershipIds.Where(x => x.Dealership_Id == dealershipAlias.Id);
query
.JoinAlias(() => sessionAlias.Dealership, () => dealershipAlias, JoinType.LeftOuterJoin)
.WithSubquery.WhereExists(dealershipIds);
var results = query.SelectList(x => x
.SelectGroup(() => seriesAlias.Id)
.SelectGroup(() => seriesAlias.Name))
.List<object[]>()
.ToDictionary(x => (long) x[0], x => (string) x[1]);
return results;
}
The exception, thrown when collecting the result, is:
An exception of type 'NHibernate.QueryException' occurred in NHibernate.dll but was not handled in user code
Additional information: could not resolve property: sessionAlias
My suspicion is that this is because dealershipIds is empty, but I'm struggling to prove that this is the case. Working with Entity Framework, it's possible to see the results of a query during debugging by unpacking its object graph. However, I can't seem to do that in NHibernate.
Is it possible to see the results of a query fragment via debugging, or do I have to pull it out with a Select statement?
The QueryOver you've posted seems to be missing the part where the
sessionAlias alias is assigned to something.
As it's being done for the others (except eventAlias which seems completely unused) the alias needs to be assigned to a property/path on the entity class being queried via JoinAlias/JoinQueryOver or to the entity itself as it's done on the QueryOver creation with seriesAlias.
Then, that alias (variable) can be used in Where, OrderBy, etc.
For example, supposing that Session is an entity referenced from a property of Series (no clue regarding your actual entity model) the following would fix your problem as it will bind sessionAlias to that property:
// This binds the alias to the property.
query.JoinAlias(s => s.Session, () => sessionAlias);
// This is the same as above, but uses the previously defined alias for the main entity,
// just to show how aliases can be used.
query.JoinAlias(() => seriesAlias.Session, () => sessionAlias);

IQueryable with AlphaNumeric sorting

I am trying to get this sorting algorithm to work with IQueryable.OrderBy():
https://www.dotnetperls.com/alphanumeric-sorting
I have the AlphanumComparatorFast class implemented and my code looks like so:
return this.DbContext.IssuesAccessView.AsQueryable()
.Include(r => r.Issue)
.Include(r => r.Issue.IssueAttachments)
.Include(r => r.Issue.IssueParticipants)
.Where(x => x.UserId == userId)
.Select(y => y.Issue)
.OrderBy(p => p.IssueNumber, new AlphanumComparatorFast());
It compiles fine but Linq has no idea to do with it at runtime because it cannot translate AlphanumComparatorFast into SQL, so I get this error
"this method cannot be translated into a store expression."
The key here is that I do not want to lose the IQueryable interface. I need to return queryable for delayed execution.
So my question is, can anyone think of a way to do this? Do I need to use a SQL function(or some such) or can this be done using IQueryable?
Thank you!
UPDATE
Thanks to CodeNotFound for answering that I do need to create a SQL function to do this.
I have created a function and am trying to call it from C# EF6 Code First(no edmx files) the problem is that I can't find a good example of how to do this. The closest I have gotten is this:
[Function(Name = "dbo.fn_CreateAlphanumericSortValue", IsComposable = true)]
[return: Parameter(DbType = "VarChar(100)")]
public string ReverseCustName([Parameter(Name = "string", DbType =
"VarChar(100)")] string #string)
{
return ((string)(this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
#string).ReturnValue));
}
The problem here is that this is from Linq to SQL and uses the DataContext.ExecutMethodCall function instead of the DBContext object used in EF 6 and there appears to be no equivalent method in DBContext
Any help on how to do this in EF6(Code first, no .edmx files) would be appreciated
You've the following error
"this method cannot be translated into a store expression."
Because your query must be translated into SQL instructions therefore there is no available clause in SQL that can be mapped with AlphanumComparatorFast.
Do I need to use a SQL function(or some such) or can this be done
using IQueryable?
Yes you need to translate the logic of your sorting written in C# composable with SQL by creating a SQL function on the server side and use it in your Linq to Entites query.

Getting values from a many-many relationship using unassociated data (LINQ)

I have the below (simplified) database set up in MVC 4/EF 6. Each class references the other using the standard public virtual ICollection<SalesOrder> SalesOrders { get; set; } (and vice versa) in its model. I'm brand new to EF and C# and whatnot (but not SQL), so this is probably very simple, but I'm unsure of how to do this: I need to write a function that returns a list of every SalesOrder.ExternalId associated with a given OrderChangeNotice.Id. This has no sort of filtering on it so I know it's wrong, but here's an idea of the queries I've been trying:
ocnRepository.SelectMany(n => n.SalesOrders.Select(o => o.ExternalId)).ToList();
How would I correctly write this query?
Simplified DB schema:
If it helps, here is the table mapping code (in the map file for OrderChangeNotice, no similar code exists in the map for SalesOrder as it is only required in one).
this.HasMany(o => o.SalesOrders)
.WithMany(o => o.OrderChangeNotices)
.Map(m =>
{
m.ToTable("OCN_SalesOrders");
m.MapLeftKey("OrderChangeNoticeId");
m.MapRightKey("SalesOrderId");
});
I'm more used to NHibernate, but I think it should be something like this:
ocnRepository.SelectMany(n => n.SalesOrders)
.Where(so => so.OrderChangeNoticeId == myOrderChangeNoticeId)
.Select(o => o.ExternalId)
.ToList();

Using a Property mapping with a Formula in NHIbernate

I am trying to map a property to an arbitrary column of another table. The docs say that the formula can be arbitrary SQL and the examples I see show similar.
However, the SQL NHibernate generates is not even valid. The entire SQL statement from the formula is being injected into the middle of the SELECT statement.
Property(x => x.Content, map =>
{
map.Column("Content");
map.Formula("select 'simple stuff' as 'Content'");
});
This is the way Formula is designed, it is supposed to work that way. You need to wrap your SQL statement in parens so that valid SQL can be generated.
Also, you cannot specify Column and Formula together. You must provide the whole SQL statement. Any non prefixed/escaped columns ('id' in the example below) will be treated as columns of the table of the owning entity.
Property(x => x.Content, map =>
{
map.Formula("(select 'simple stuff' as 'Content')");
});
// or what you probably want
Property(x => x.Content, map =>
{
map.Formula("(select t.Content FROM AnotherTable t WHERE t.Some_id = id)");
});

Optimising Linq to Entities

I have a set of related entities. I'm using linq to group a collection of an entity type by a property on a related entity and then doing a sum calculation on a property of another related entity:
Vehicles.GroupBy(v => v.Mechanics.Engine.Size)
.Select(g => g.Sum(s => s.Passengers.Count));
I'm trying to do as much as possible via linq to entities because there is a large number of records in the db. However, the generated sql includes 9 select statements and an outer apply which takes more than 5 times as long to execute as writing the simplified sql code to achieve the same in one select statement.
How do I improve the generated sql?
You're in fact counting the number of passengers per engine size. So, the navigation properties permitting, you could also do:
Passengers.GroupBy(p => p.Vehicle.Mechanics.Engine.Size)
.Select(g => g.Count())
This will probably generate more joins and less subqueries. And only one aggregating statement in stead of two in the original query, of which one (Count) is repeated for each size.
Perhaps try the query like this:
Vehicles
.Select(x => new
{
EngineSize = x.Mechanics.Engine.Size,
PassengersCount = xs.Passengers.Count,
})
.ToArray()
.GroupBy(v => v.EngineSize)
.Select(g => g.Sum(s => s.PassengersCount));
This will execute in a single query, but may pull back too much data to make it faster. It's worth timing and profiling to see which is better.
You could also consider a hybrid approach whereby you bypass LINQ query generation yet use EF to project results into strong types like this:
public List<Vechicles> GetVehcileInformation(string VehicleType){
var QueryString = Resources.Queries.AllVehicles;
var parms = new List<SqlParameters>();
parms.Add(new SqlParameter("VehicleType", VehicleType );
try{
using (var db = new MyEntities()){
var stuff= db.SqlQuery<Vehicles>(QueryString, parms.ToArray());
return stuff.ToList();
}
}catch(exception iox){Log.ErrorMessage(iox);}
}
The idea is that the group by is done at DB layer which gives you more control than in LINQ. You get the speed of direct SQL Queries but get back strongly typed results! The query string itself is stored in a resources file as a string with Parameter place holders like this:
Select * from Table Where FieldName = #VehicleType...

Resources