C
C#•2y ago
Dropps

❔ efcore querying

hello there iam right now digging a bit deeper into ef core and how to query data correctly but i dont get my head around something maybe someone can help i have following data classes: 
namespace ExampleApp.Tests.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; }
}
namespace ExampleApp.Tests.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; }
}
namespace ExampleApp.Tests.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; }
}
namespace ExampleApp.Tests.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; }
}
i have a table for both of these classes now my question is how do i handle following behaviour: GetAllAsync(Guid? userId) GetByIdAsync(Guid productId, Guid? userId) goal is to include the rating the user gove if the userid isnt null if it is then return just the product model but i dont get around how to do it this is what i tried 
        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.Rating)
                .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.Rating)
                .FirstOrDefaultAsync(f => f.Id == id, cancellationToken: token);
        }
29 Replies
TravestyOfCode
TravestyOfCode•2y ago
Do you want two separate queries, GetAllAsync as well as GetByIdAsync? Or do you want to try and combine them into one query with a nullable userId (like your GetByIdAsync shows)?
Dropps
DroppsOP•2y ago
those are 2 seperate querys GetAllAsync is a query which has a nullable argument userId if it is null then just return all products if userId is not null then include the rating from the user from the ProductRatings table
TravestyOfCode
TravestyOfCode•2y ago
It doesn't look like the Product table has a defined relation to the ProductRating table, so I think you might need to use a .Join as part of your query to include ProductRating, or add a navigational properties to your tables.
Dropps
DroppsOP•2y ago
i dont think so because i defined the relation in the model builder 
    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)
            .IsRequired();
        
        modelBuilder.Entity<Product>()
            .Property(f => f.Rating)
            .IsRequired(false);
        
        modelBuilder.Entity<Product>()
            .Property(f => f.UserRating)
            .IsRequired(false);
        
        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);
    }
    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)
            .IsRequired();
        
        modelBuilder.Entity<Product>()
            .Property(f => f.Rating)
            .IsRequired(false);
        
        modelBuilder.Entity<Product>()
            .Property(f => f.UserRating)
            .IsRequired(false);
        
        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);
    }
TravestyOfCode
TravestyOfCode•2y ago
Doesn't look like you have any relations defined. I'm assuming that a Product can have multiple ProductRating?
Dropps
DroppsOP•2y ago
yes more than one user can rate a product
TravestyOfCode
TravestyOfCode•2y ago
So you're going to want to add some navigation properties and update the configuration to inform about the relationship: 
public class Product
{
   // Other properties ...
   public ICollection<ProductRating> ProductRatings { get; set; }
}

public class ProductRating
{
    // Other properties
    public Product Product { get; set; }
    public AppUser User { get; set; } // IdentityUser or whatever your user class is
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Other configs
    modelBuilder.Product
        .HasMany(p => p.ProductRatings)
        .WithOne(p => p.Product)
        .HasForeignKey(p => p.ProductId);

    modelBuilder.ProductRating
         .HasOne(p => p.User)
         .WithMany()
         .HasForeignKey(p => p.UserId);
}
public class Product
{
   // Other properties ...
   public ICollection<ProductRating> ProductRatings { get; set; }
}

public class ProductRating
{
    // Other properties
    public Product Product { get; set; }
    public AppUser User { get; set; } // IdentityUser or whatever your user class is
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Other configs
    modelBuilder.Product
        .HasMany(p => p.ProductRatings)
        .WithOne(p => p.Product)
        .HasForeignKey(p => p.ProductId);

    modelBuilder.ProductRating
         .HasOne(p => p.User)
         .WithMany()
         .HasForeignKey(p => p.UserId);
}
Then you can include these in your query:
Dropps
DroppsOP•2y ago
the userId i only get from the jwt token by my identity solution i dont save userdata myself and you are right i forgot the forgein keys
TravestyOfCode
TravestyOfCode•2y ago
Yeah, the User was only if you wanted to navigate to your user from ProductRating otherwise you can exclude it. Then you can query something like: 
if (userId == null)
{
    return await _dbContext.Product.FirstOrDefaultAsync(f => f.Id == id, token);
}
else
{
   return await _dbContext.Product
        .Include(p => p.ProductRating)
        .Where(p => p.Id == id && p => p.ProductRating.UserId == userId)
        .FirstOrDefaultAsync(token);        
}
if (userId == null)
{
    return await _dbContext.Product.FirstOrDefaultAsync(f => f.Id == id, token);
}
else
{
   return await _dbContext.Product
        .Include(p => p.ProductRating)
        .Where(p => p.Id == id && p => p.ProductRating.UserId == userId)
        .FirstOrDefaultAsync(token);        
}
(code might need some cleanup as I just typed it in discord and not IDE)
Dropps
DroppsOP•2y ago
hmm but how do i handle the extra property on the model classes now? should i split them into database models and business layer models?
TravestyOfCode
TravestyOfCode•2y ago
The extra property? You mean the ProductRatings on the Product class and Product on the ProductRating table?
Dropps
DroppsOP•2y ago
yes (iam migrating from dapper to ef core)
TravestyOfCode
TravestyOfCode•2y ago
They're called Navigation properties, they don't actually create a column in your database as long as you've defined them as part of the relation.
TravestyOfCode
TravestyOfCode•2y ago
Dropps
DroppsOP•2y ago
thats not what i meant i have a converter class to get from request to model and from model to response
TravestyOfCode
TravestyOfCode•2y ago
Oh, like DTO to domain?
Dropps
DroppsOP•2y ago
using ExampleApp.Tests.Application.Models;
using ExampleApp.Tests.Contract.Requests;
using ExampleApp.Tests.Contract.Responses;

namespace ExampleApp.Tests.Api.Mapping;

public static class ContractMapping
{
    public static Product ToModel(this CreateProductRequest request)
    {
        return new Product
        {
            Id = Guid.NewGuid(),
            Name = request.Name,
            Description = request.Description,
            Price = request.Price,
        };
    }
    
    public static Product ToModel(this UpdateProductRequest request, Guid id)
    {
        return new Product
        {
            Id = id,
            Name = request.Name,
            Description = request.Description,
            Price = request.Price,
        };
    }
    
    public static ProductResponse ToContract(this Product product)
    {
        return new ProductResponse
        {
            Id = product.Id,
            Name = product.Name,
            Description = product.Description,
            Price = product.Price,
        };
    }
    
    public static ProductsResponse ToContract(this IEnumerable<Product> products)
    {
        return new ProductsResponse
        {
            Items = products.Select(ToContract)
        };
    }
}
using ExampleApp.Tests.Application.Models;
using ExampleApp.Tests.Contract.Requests;
using ExampleApp.Tests.Contract.Responses;

namespace ExampleApp.Tests.Api.Mapping;

public static class ContractMapping
{
    public static Product ToModel(this CreateProductRequest request)
    {
        return new Product
        {
            Id = Guid.NewGuid(),
            Name = request.Name,
            Description = request.Description,
            Price = request.Price,
        };
    }
    
    public static Product ToModel(this UpdateProductRequest request, Guid id)
    {
        return new Product
        {
            Id = id,
            Name = request.Name,
            Description = request.Description,
            Price = request.Price,
        };
    }
    
    public static ProductResponse ToContract(this Product product)
    {
        return new ProductResponse
        {
            Id = product.Id,
            Name = product.Name,
            Description = product.Description,
            Price = product.Price,
        };
    }
    
    public static ProductsResponse ToContract(this IEnumerable<Product> products)
    {
        return new ProductsResponse
        {
            Items = products.Select(ToContract)
        };
    }
}
TravestyOfCode
TravestyOfCode•2y ago
You can use projection in the query to map a domain model to another type. It looks something like: 
return await _dbContext.Product
        .Include(p => p.ProductRating)
        .Where(p => p.Id == id && p => p.ProductRating.UserId == userId)
        .Select(p => new ProductResponse()
        {
            Id = p.Id,
            Name = p.Name,
            // etc.
            UserRating = p.ProductRating.Rating
        }
        .FirstOrDefaultAsync(token);
return await _dbContext.Product
        .Include(p => p.ProductRating)
        .Where(p => p.Id == id && p => p.ProductRating.UserId == userId)
        .Select(p => new ProductResponse()
        {
            Id = p.Id,
            Name = p.Name,
            // etc.
            UserRating = p.ProductRating.Rating
        }
        .FirstOrDefaultAsync(token);
Dropps
DroppsOP•2y ago
no thats not related to the query part the response and request only gets converted when i go outside from the business layer i.e. at entrace to the api and at exit to the api
TravestyOfCode
TravestyOfCode•2y ago
Does your ProductResponse have a property for the user rating, either an int? or a ProductRatingResponse object? In ToContract you would need to check for a null ProductRating as part of the product. If you have a int? called UserRating you would add if (product.ProductRating != null) UserRating = product.ProductRating.Rating; to your ToContract
Dropps
DroppsOP•2y ago
let me show 
namespace ExampleApp.Tests.Contract.Responses;

public class ProductResponse
{
    public required Guid Id { get; init; }
    public required string Name { get; init; }
    public required string? Description { get; init; }
    public required decimal Price { get; init; }
}
namespace ExampleApp.Tests.Contract.Responses;

public class ProductResponse
{
    public required Guid Id { get; init; }
    public required string Name { get; init; }
    public required string? Description { get; init; }
    public required decimal Price { get; init; }
}
namespace ExampleApp.Tests.Contract.Responses;

public class ProductsResponse
{
    public required IEnumerable<ProductResponse> Items { get; init; } = Enumerable.Empty<ProductResponse>();
}
namespace ExampleApp.Tests.Contract.Responses;

public class ProductsResponse
{
    public required IEnumerable<ProductResponse> Items { get; init; } = Enumerable.Empty<ProductResponse>();
}
namespace ExampleApp.Tests.Contract.Requests;

public class CreateProductRequest
{
    public required string Name { get; init; }
    public required string Description { get; init; }
    public required decimal Price { get; init; }
}
namespace ExampleApp.Tests.Contract.Requests;

public class CreateProductRequest
{
    public required string Name { get; init; }
    public required string Description { get; init; }
    public required decimal Price { get; init; }
}
namespace ExampleApp.Tests.Contract.Requests;

public class RateProductRequest
{
    public required int Rating { get; init; }
    public required string? Comment { get; init; }
}
namespace ExampleApp.Tests.Contract.Requests;

public class RateProductRequest
{
    public required int Rating { get; init; }
    public required string? Comment { get; init; }
}
namespace ExampleApp.Tests.Contract.Requests;

public class UpdateProductRequest
{
    public required string Name { get; init; }
    public required string Description { get; init; }
    public required decimal Price { get; init; }
}
namespace ExampleApp.Tests.Contract.Requests;

public class UpdateProductRequest
{
    public required string Name { get; init; }
    public required string Description { get; init; }
    public required decimal Price { get; init; }
}
on api side things like userid and product id are also retrived via httpcontext and route parameters
TravestyOfCode
TravestyOfCode•2y ago
Your ProductResponse has no idea about ratings, though.
Dropps
DroppsOP•2y ago
yea i still need to update that the product response gives a nullable rating and userrating
TravestyOfCode
TravestyOfCode•2y ago
So ProductResponse will have public int? UserRating { get; set; } which is where you want to have the ProductRating.Rating value stored if the user has rated the product, yes?
Dropps
DroppsOP•2y ago
yes and the float? Rating will be the average of all ratings that there are (for that product id)
TravestyOfCode
TravestyOfCode•2y ago
So you're still going to want to have the navigation property on Product to link it to all of the ProductRating. Then something like: 
_dbContext.Product
.Include(p => p.ProductRating)
.Select(p => new Product()
{
    Id = p.Id,
    Name = p.Name,
    AverageRating = p.ProductRating.Select(r => r.Rating).DefaultIfEmpty().Average(),
    UserRating = userId == null ? null : p.ProductRating.FirstOrDefault(pr => pr.UserId == userId)?.Rating
}).FirstOrDefault();
_dbContext.Product
.Include(p => p.ProductRating)
.Select(p => new Product()
{
    Id = p.Id,
    Name = p.Name,
    AverageRating = p.ProductRating.Select(r => r.Rating).DefaultIfEmpty().Average(),
    UserRating = userId == null ? null : p.ProductRating.FirstOrDefault(pr => pr.UserId == userId)?.Rating
}).FirstOrDefault();
I think. Might need to tweak the Select stuff, though as I may have typos.
Dropps
DroppsOP•2y ago
hmm i think i gonna continue this tomorrow my brain stops working thanks a lot tho
TravestyOfCode
TravestyOfCode•2y ago
👍
Accord
Accord•2y 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.
Want results from more Discord servers?
Add your server