phpmyadmin Question posted in
The official documentation can be found here.

Phpmyadmin – C# EF Core MySQL to LINQ return nothing

var IdSubProduct = new MySqlConnector.MySqlParameter("IdSubProduct", this.parameter.Id.ToString());

FormattableString cmd = $@"SELECT spr.*, c.Name CustomerName FROM ProductSubProductRate spr 
LEFT JOIN LibraryCustomer c ON (c.Id = spr.CustomerId)
WHERE (spr.IdSubProduct = {IdSubProduct})";
this.contextSQL.ProductSubProductRate.FromSql(cmd).ToList();

this.contextSQL.ProductSubProductRate
.Include("Customers")
.Where(pr => pr.IdSubProduct == this.parameter.Id.ToString())
.Select(pr => new
{
    pr.Id,
    pr.EffectiveDate,
    pr.Rate,
    pr.Additional,
    pr.Creator,
    pr.Modifier,
    CustomerName = pr.Customers.Name
})
.OrderByDescending(pr => pr.EffectiveDate).ToList();

Is this not the same? Why the SQL return exactly the result I need, while the LINQ return nothing. Table relation is done as what’s written here. Even if I remove the .Include, still returned nothing.

Edit : I removed some annotations to make it simple. With this model I already dropped everything in phpmyadmin and reuse .EnsureCreated(). Also checked relation view’s db, table and column which is LibraryCustomer’s Id.

I think EF should use LEFT JOIN automatically since I set PK CustomerId as nullable. There is no reason to use INNER JOIN for something that is technically can be null. But I will continue the search.

Here’s the DBcontext OnModelCreating event. There is no .IsRequired() too.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<modelLibraryCustomer>()
        .HasMany(e => e.SubProductRates)
        .WithOne(e => e.Customers)
        .HasForeignKey(e => e.CustomerId)
        .HasPrincipalKey(e => e.Id);
}

And here’s both model.

    [Index(nameof(IdSubProduct), nameof(EffectiveDate), IsUnique = true)]
    internal class modelDataProductSubProductRate
    {
        [Key]
        public string Id { get; set; }
        public string IdSubProduct { get; set; }
        public DateOnly EffectiveDate { get; set; }
        public decimal Rate { get; set; }
        public string Additional { get; set; }

#nullable enable
        //[ForeignKey("IdCustomer")]
        public string? CustomerId { get; set; }
#nullable disable
        public modelLibraryCustomer Customers { get; set; }

        public virtual string? CustomerName { get; set; }
    }

[Index(nameof(Name), IsUnique = true)]
internal class modelLibraryCustomer
{
    [Key]
    public string Id { get; set; }
    public string Name { get; set; }
    public string CompanyName { get; set; }
    public string Telephone { get; set; }
    public string Additional { get; set; }
    public bool Active { get; set; }

    public ICollection<modelDataProductSubProductRate> SubProductRates { get; }
}
Tags:

2

Answers


  1. 0

    You can try to change include method as navigation property

    this.contextSQL.ProductSubProductRate
.Include(pr=>pr.Customers)
.Where(pr => pr.IdSubProduct == this.parameter.Id.ToString())
.Select(pr => new
{
    pr.Id,
    pr.EffectiveDate,
    pr.Rate,
    pr.Additional,
    pr.Creator,
    pr.Modifier,
    CustomerName = pr.Customers.Name
})
.OrderByDescending(pr => pr.EffectiveDate).ToList();
    Login or Signup to reply.
  2. 0

    I see two problems:

    • The one-to-many relation between Customers and SubProductRates is not declared virtual
    • Not sure if this is a cause of a problem: the foreign key SubProductRate.CustomerId can be null.

    In entity framework the columns of your tables are represented by non-virtual properties, the virtual properties represent the relations between the tables (one-to-many, many-to-many, …)

    Apparently there is a one-to-many relation between Customers and ProductRates: Every Customer created zero or more ProductRates, every ProductRate is the ProductRate of exactly one Customer, namely the Customer that property ProductRate.CustomerId refers to.

    You defined this one-to-many relation using API:

    modelBuilder.Entity<modelLibraryCustomer>()
    .HasMany(e => e.SubProductRates)
    .WithOne(e => e.Customers)
    .HasForeignKey(e => e.CustomerId)
    .HasPrincipalKey(e => e.Id);

    Here you state that every SubProductRate has exactly one Customer, namely the Customer that property SubProductRate.CustomerId refers to. Be aware, CustomerId cannot be null, because that would mean that some SubProductRates would have zero Customers, while you just said that every SubProductRate has exactly one Customer.

    If you’d stuck to entity framework conventions, you would have had classes similar to these:

    class Customer
{
    public string Id { get; set; }
    
    public string Name { get; set; }
    public string CompanyName { get; set; }
    ... // etc, other properties

    // Every Customer created one or more SubProductRates (one-to-many)
    public virtual ICollection<SubProductRate> SubProductRates { get; }
}

class SubProductRate
{
    public string Id { get; set; }
    public string IdSubProduct { get; set; }
    ... // etc

    // every SubProductRate belongs to exactly one Customer, using foreign key
    public string CustomerId { get; set; }
    public virtual Customer Customer {get; set;}
}

    Note: CustomerId is a column in table SubProductRate. Hence it is non-virtual. Property Customer describes the relation between SubProductRates and Customers, hence it is virtual: every SubProductRate belongs to exactly one Customer.

    For completeness:

    class MyDbContext : DbContext
{
    public DbSet<Customer> Customers {get; set;}
    public DbSet<SubProductRate> SubProductRates {get; set;}
    ... // other tables
}

    Because these classes stuck to the conventions, entity framework can detect the columns of the tables and the relations between the tables. There is no need for attributes not fluent API.

    Only if you decide to deviate from the conventions, use non-standard column names, or non-standard names for the relation between the two tables, you need to give entity framework more information using attributes or fluent API.

    Hence, I advice you, whenever possible, try to stick to the conventions. Only if you cannot define the names of the tables and their columns, deviate from it and use fluent API.

    Back to your question

    Although you didn’t specify what you wanted, you only gave us a SQL statement, I think that you want (several) properties from all SubProductRates, each with (several) properties of the Customer that this SubProductRate belongs to.

    How about this:

    var query = myDbContext.SubProductrates.
    .Where(subProductRate => ...)         // only if you don't want all SubProductRates
    . Select(subProductRate => new
    {
        // Select only the properties that you actually plan to use
        Id = subProductRate.Id,
        Name = subProductRate.Name,
        ...

        // The Customer that this SubProductRate belongs to:
        Customer = new
        {
            // again, only the properties that you plan to use:
            Id = subProductRate.Customer.Id,
            Name = subProductRate.Customer.Name,
            ...
        },

        // foreign key to customer not needed, you already know the value:
        // CustomerId = subProductrate.CustomerId,
    })
    .OrderByDescending(subProductRate => subProductRate.EffectiveDate);

    It improves readability if you stick to the convention that collections are represented by plural nouns. Elements of collections are represented by singular nouns.

    You could also Select the other way around: give me all …, each with their zero or more …

    Requirement: Give me all (or some) Customers, each with all (or some of ) their zero or more SubProductRates

    var query = myDbContext.Customers
    .Where(customer => ...)           // if you don't want all Customers
    .Select(customer => new
    {
        Id = customer.Id,
        Name = customer.Name,
        ...

        SubProductRates = customer.SubProductRates

            // only if you don't want all subProductRates of this customer
            // for example, only the Active ones:
            .Where(subProductRate => subProductRate.Active)

            .Select(subProductRate => new
            {
                Id = subProductRate.Id,
                Name = subProductRate.Name,
                ...
            })
            .ToList(),
    });

    Avoid using Include!

    Database management systems are extremely optimized for combining and selecting data. One of the slower parts of a query is the transfer of the selected data to your local process. Hence it is wise to limit the transfer of your data to the values that you actually plan to use.

    Always use Select when querying information, and Select only the properties that you actually plan to use. Only fetch the complete object or use Include if you plan to change the fetched data.

    The reason for this is efficiency. If you fetch the complete object or use Include, you also fetch properties that you won’t use, or properties that you already know the value of.

    For instance, if you have a one-to-many relationship between Schools and Students, every Student will have a foreign key to the School he attends. If you query School [10] with all its 2000 Students, then every Student will have a value for property SchoolId that equals 10. What a waste of processing power to transfer this value 10 over 2000 times!

    Another more important reason not to use Include, is that the fetched data will be put in the ChangeTracker.

    Every DbContext has a ChangeTracker. Every complete object that you fetch (so without Select, or using Include), will be put in the ChangeTracker, as well as a Clone of it. You get the reference of the clone (or the original, doesn’t matter), and if you update values of fetched data, they will be changed in the clone. If you call SaveChanges, then every property in the Clone will be value checked with the original. Only the changed ones will be sent to the database.

    So again, if you Select school [10] with its 2000 Students, only to display their Names and Addresses, it would be a huge waste of processing power if you’d use Include. Not only will you have transferred way more values than you’ll use, you’ll also will have cloned all 2000 Students. If you decide to change the name of the School, all properties of all 2000 original Students will be value checked with their 2000 clones, while you only needed to check the properties of the School.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search