Info on GroupBy #11887
Labels
Comments
|
@berets76 - What do you mean by "not generated properly good"? Can you elaborate what is current output and what is the output you are expecting? |
|
@smitpatel I'm expecting a GROUP BY clause or at least a single query with joins for navigation properties, instead I've the following output: tons of warning about locally evaluated FirstOrDefault and different queries for each related property I access. Test caseusing System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace EFSampleApp
{
public class Program
{
public static void Main(string[] args)
{
using (var db = new MyContext())
{
// Run queries
Guid TenantID = Guid.Parse("6e359223-1b33-c541-80a2-0003ee5302d4");
Guid CompanyID = Guid.Parse("ca74311d-6211-e001-80d2-0003ac5302d4");
string Filter = string.Empty;
var data = db.Set<GuestMovement>()
.GroupBy(g => new { g.GuestID }, (key, dat) => dat.OrderByDescending(o => o.When).FirstOrDefault())
.Where(w => w.TenantID == TenantID)
.Select(s => new FacilityGuestViewModel()
{
When = s.When,
ID = s.ID,
GuestID = s.GuestID,
Name = s.Guest.First + " " + s.Guest.Last,
VAT = s.Guest.VAT
})
.ToList();
Console.WriteLine("Results count : " + data.Count.ToString());
}
Console.WriteLine("Program finished.");
Console.ReadLine();
}
}
public class MyContext : DbContext
{
private static ILoggerFactory LoggerFactory => new LoggerFactory().AddConsole(LogLevel.Trace);
public string LanguageID => "it";
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connection = @"Server=tcp:myserver.database.windows.net,1433;Initial Catalog=mydatabase;Persist Security Info=False;User ID=myuser;Password=mypwd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
optionsBuilder
.UseSqlServer(connection)
.EnableSensitiveDataLogging()
.UseLoggerFactory(LoggerFactory);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Configure model
// Country
modelBuilder.Entity<Country>().HasKey(c => new { c.TenantID, c.ID });
modelBuilder.Entity<Country>().Property(p => p.ID).ValueGeneratedOnAdd().HasDefaultValueSql("newsequentialid()");
// CountryName
modelBuilder.Entity<CountryName>().HasKey(c => new { c.TenantID, c.CountryID, c.LanguageID });
modelBuilder.Entity<CountryName>().HasOne(ho => ho.Country).WithMany(wm => wm.Names).HasForeignKey(fk => new { fk.TenantID, fk.CountryID }).OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<CountryName>().HasQueryFilter(qf => qf.LanguageID == LanguageID);
// City
modelBuilder.Entity<City>().HasKey(c => new { c.TenantID, c.CountryID, c.ID });
modelBuilder.Entity<City>().Property(p => p.ID).ValueGeneratedOnAdd().HasDefaultValueSql("newsequentialid()");
// CityName
modelBuilder.Entity<CityName>().HasKey(c => new { c.TenantID, c.CountryID, c.CityID, c.LanguageID });
modelBuilder.Entity<CityName>().HasOne(ho => ho.City).WithMany(wm => wm.Names).HasForeignKey(fk => new { fk.TenantID, fk.CountryID, fk.CityID }).OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<CityName>().HasQueryFilter(qf => qf.LanguageID == LanguageID);
// Guest
modelBuilder.Entity<Guest>().HasKey(c => new { c.TenantID, c.ID });
modelBuilder.Entity<Guest>().Property(p => p.ID).ValueGeneratedOnAdd().HasDefaultValueSql("newsequentialid()");
modelBuilder.Entity<Guest>().HasOne(ho => ho.BirthCountry).WithMany().HasForeignKey(fk => new { fk.TenantID, fk.BirthCountryID }).OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<Guest>().HasOne(ho => ho.BirthCity).WithMany().HasForeignKey(fk => new { fk.TenantID, fk.BirthCountryID, fk.BirthCityID }).OnDelete(DeleteBehavior.Restrict);
// GuestMovement
modelBuilder.Entity<GuestMovement>().HasKey(c => new { c.TenantID, c.CompanyID, c.GuestID, c.ID });
modelBuilder.Entity<GuestMovement>().Property(p => p.ID).ValueGeneratedOnAdd().HasDefaultValueSql("newsequentialid()");
modelBuilder.Entity<GuestMovement>().HasOne(ho => ho.Guest).WithMany().HasForeignKey(fk => new { fk.TenantID, fk.GuestID }).OnDelete(DeleteBehavior.Restrict);
}
}
[Table("Guests")]
public class Guest
{
[Key, Column(Order = 0)]
public Guid TenantID { get; set; }
[Key, Column(Order = 1)]
public Guid ID { get; set; }
public Guid? BirthCountryID { get; set; }
public virtual Country BirthCountry { get; set; }
public Guid? BirthCityID { get; set; }
public virtual City BirthCity { get; set; }
public DateTime? Cancelled { get; set; }
public string VAT { get; set; }
public string First { get; set; }
public string Last { get; set; }
public DateTime BirthDate { get; set; }
public string ResidenceAddress { get; set; }
[NotMapped]
public string FullName => First + " " + Last;
public bool IsHospitalized { get; set; }
public bool IsPresent { get; set; }
public short ReservationsNumber { get; set; }
public string FacilityCode { get; set; }
}
[Table("Countries")]
public class Country
{
[Key, Column(Order = 0)]
public Guid TenantID { get; set; }
[Key, Column(Order = 1)]
public Guid ID { get; set; }
public virtual List<CountryName> Names { get; set; }
}
[Table("CountryNames")]
public class CountryName
{
[Key, Column(Order = 0)]
public Guid TenantID { get; set; }
[Key, Column(Order = 1)]
public Guid CountryID { get; set; }
[Key, Column(Order = 2)]
[StringLength(3)]
public string LanguageID { get; set; }
[StringLength(255)]
public string Name { get; set; }
public virtual Country Country { get; set; }
}
[Table("Cities")]
public class City
{
[Key, Column(Order = 0)]
public Guid TenantID { get; set; }
[Key, Column(Order = 1)]
public Guid CountryID { get; set; }
[Key, Column(Order = 2)]
public Guid ID { get; set; }
public virtual List<CityName> Names { get; set; }
}
[Table("CityNames")]
public class CityName
{
[Key, Column(Order = 0)]
public Guid TenantID { get; set; }
[Key, Column(Order = 1)]
public Guid CountryID { get; set; }
[Key, Column(Order = 2)]
public Guid CityID { get; set; }
[Key, Column(Order = 3)]
[StringLength(3)]
public string LanguageID { get; set; }
[StringLength(255)]
public string Name { get; set; }
public virtual City City { get; set; }
}
[Table("GuestMovements")]
public class GuestMovement
{
[Key, Column(Order = 0)]
public Guid TenantID { get; set; }
[Key, Column(Order = 1)]
public Guid CompanyID { get; set; }
[Key, Column(Order = 2)]
public Guid GuestID { get; set; }
[Key, Column(Order = 3)]
public Guid ID { get; set; }
public DateTime When { get; set; }
public virtual Guest Guest { get; set; }
public DateTime? Cancelled { get; set; }
}
public class FacilityGuestViewModel
{
public DateTime When { get; set; }
public Guid ID { get; set; }
public Guid GuestID { get; set; }
public string VAT { get; set; }
public string Name { get; set; }
public string AssignedPlace { get; set; }
}
}Output |
UpdateIf you change the .Select adding nested navigation properties (Guest.BirthCity) you'll got an Exception at runtime
Stacktrace
It seems that lazy loading was ignored. |
|
For the second query & exception you posted. I attempted to create a repro with your code above. https://gist.github.com/smitpatel/69ee32380b31ce9a7c41eae7506c63e5 |
|
@smitpatel simply change the query to But if you have no data no exception was thrown. |
|
p.s: I test it with 2.1.0-rc1-final today, same result |
|
The main issue here is this o -> GuestMovement Assigning to @maumar |
|
@smitpatel regarding your first problem answer
How can I, actually, get only first item from an ordered grouped query ? Sample Filter posts by BlogID, grouping by Category, ordering descending by PostDate and retrieve only the latest (newest PostDate) item (with all its properties and navigation properties) for each Category. Data Expected results Last column (with 2 value) is the count of grouped items. I tried different ways but every time I got "locally executed" with a lot of small queries for each item. |
|
FromSql with QueryTypes is the simplest way to get the data. |
|
Duplicate of #12088 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Actually (2.1.o-preview2-final) this query
is not generated properly good, is it correct ?
Or is there another syntax compared to EF6 (origin of this query) ?
Further technical details
EF Core version: 2.1.0-preview2-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 64 bit
IDE: Visual Studio 2017 15.7.0 Preview 5
The text was updated successfully, but these errors were encountered: