C#•13mo ago

❔ EF core querying issue

hello there i got a question around modeling... in short: i have a Product model and a ProductRatings model i have for both of them a table and want to join in the average rating and user specific rating into the response when you request it via the api so i forward it from controller to service and from service to repository there i want then to create my queries model classes:

namespace DroppsDev.iFoodTracker.Application.Models;

public class Product
{
    /// <summary>
    /// Unique identifier for the Product.
    /// </summary>
    public Guid Id { get; set; }
    
    /// <summary>
    /// Name of the Product.
    /// </summary>
    public string Name { get; set; } = string.Empty;
    
    /// <summary>
    /// Description of the Product.
    /// </summary>
    public string? Description { get; set; }
    
    /// <summary>
    /// Price of the Product.
    /// </summary>
    public decimal Price { get; set; }
    
    /// <summary>
    /// Average rating of the Product.
    /// </summary>
    public float? Rating { get; set; }
    
    /// <summary>
    /// Rating of the Product by the user.
    /// </summary>
    public int? UserRating { get; set; }

    /// <summary>
    /// Navigation property for the ProductRating.
    /// </summary>
    public IEnumerable<ProductRating> ProductRatings { get; set; }
}

namespace DroppsDev.iFoodTracker.Application.Models;

public class Product
{
    /// <summary>
    /// Unique identifier for the Product.
    /// </summary>
    public Guid Id { get; set; }
    
    /// <summary>
    /// Name of the Product.
    /// </summary>
    public string Name { get; set; } = string.Empty;
    
    /// <summary>
    /// Description of the Product.
    /// </summary>
    public string? Description { get; set; }
    
    /// <summary>
    /// Price of the Product.
    /// </summary>
    public decimal Price { get; set; }
    
    /// <summary>
    /// Average rating of the Product.
    /// </summary>
    public float? Rating { get; set; }
    
    /// <summary>
    /// Rating of the Product by the user.
    /// </summary>
    public int? UserRating { get; set; }

    /// <summary>
    /// Navigation property for the ProductRating.
    /// </summary>
    public IEnumerable<ProductRating> ProductRatings { get; set; }
}

namespace DroppsDev.iFoodTracker.Application.Models;

public class ProductRating
{
    /// <summary>
    /// Unique identifier for the Product.
    /// </summary>
    public required Guid ProductId { get; init; }
    
    /// <summary>
    /// Unique identifier for the user.
    /// </summary>
    public required Guid UserId { get; init; }
    
    /// <summary>
    /// Rating of the Product by the user. 1-5.
    /// </summary>
    public required int Rating { get; init; }
    
    /// <summary>
    /// Comment on the Product by the user.
    /// </summary>
    public required string? Comment { get; init; }
    
    /// <summary>
    /// Navigation property for the Product.
    /// </summary>
    public Product Product { get; set; }
}

namespace DroppsDev.iFoodTracker.Application.Models;

public class ProductRating
{
    /// <summary>
    /// Unique identifier for the Product.
    /// </summary>
    public required Guid ProductId { get; init; }
    
    /// <summary>
    /// Unique identifier for the user.
    /// </summary>
    public required Guid UserId { get; init; }
    
    /// <summary>
    /// Rating of the Product by the user. 1-5.
    /// </summary>
    public required int Rating { get; init; }
    
    /// <summary>
    /// Comment on the Product by the user.
    /// </summary>
    public required string? Comment { get; init; }
    
    /// <summary>
    /// Navigation property for the Product.
    /// </summary>
    public Product Product { get; set; }
}

tables that get created by ef (see image) model creation override:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasKey(f => f.Id);
        
        modelBuilder.Entity<Product>()
            .Property(f => f.Name)
            .IsRequired();
        
        modelBuilder.Entity<Product>()
            .Property(f => f.Price)
            .HasPrecision(18,2)
            .IsRequired();

        modelBuilder.Entity<Product>()
            .Property(f => f.Description)
            .IsRequired(false);
        
        modelBuilder.Entity<ProductRating>()
            .HasKey(fr => new
            {
                Id = fr.ProductId, fr.UserId
            });

        modelBuilder.Entity<ProductRating>()
            .Property(fr => fr.Rating)
            .IsRequired();
        
        modelBuilder.Entity<ProductRating>()
            .Property(fr => fr.Comment)
            .IsRequired(false);

        modelBuilder.Entity<Product>()
            .HasMany(p => p.ProductRatings)
            .WithOne(p => p.Product)
            .HasForeignKey(p => p.ProductId);
    }

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasKey(f => f.Id);
        
        modelBuilder.Entity<Product>()
            .Property(f => f.Name)
            .IsRequired();
        
        modelBuilder.Entity<Product>()
            .Property(f => f.Price)
            .HasPrecision(18,2)
            .IsRequired();

        modelBuilder.Entity<Product>()
            .Property(f => f.Description)
            .IsRequired(false);
        
        modelBuilder.Entity<ProductRating>()
            .HasKey(fr => new
            {
                Id = fr.ProductId, fr.UserId
            });

        modelBuilder.Entity<ProductRating>()
            .Property(fr => fr.Rating)
            .IsRequired();
        
        modelBuilder.Entity<ProductRating>()
            .Property(fr => fr.Comment)
            .IsRequired(false);

        modelBuilder.Entity<Product>()
            .HasMany(p => p.ProductRatings)
            .WithOne(p => p.Product)
            .HasForeignKey(p => p.ProductId);
    }

my issue then lies in the repo itself see here:

        public async Task<Product?> GetByIdAsync(Guid id, Guid? userId = null, CancellationToken token = default)
        {
            if (userId == null)
            {
                return await _dbContext.Products.FindAsync(new object?[] { id }, cancellationToken: token);
            }

            return await _dbContext.Products
                .Include(f => f.ProductRatings)
                .FirstOrDefaultAsync(f => f.Id == id, cancellationToken: token);
        }

        public async Task<Product?> GetByIdAsync(Guid id, Guid? userId = null, CancellationToken token = default)
        {
            if (userId == null)
            {
                return await _dbContext.Products.FindAsync(new object?[] { id }, cancellationToken: token);
            }

            return await _dbContext.Products
                .Include(f => f.ProductRatings)
                .FirstOrDefaultAsync(f => f.Id == id, cancellationToken: token);
        }

that includes the entire table to the nav property but does not assign the values into the properties i need to can convert into the response i need

5 Replies

boiled goose•13mo ago

so, that if (userId == null) part... i would avoid having that logic inside the query method what does this mean

does not assign the values into the properties

Dropps•13mo ago

property UserRating and Rating stays null as they arent assigned from the query (in the object of type Product)

boiled goose•13mo ago

we haven't got much to test are you sure you are hitting the right query? are you sure your data is complete? did you look into the db context at debug time to see if there are all the fields

Dropps•13mo ago

yes there are

Accord•13mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ EF core querying issue