I am new to Entity Framework and Linq. I am using .Net Core and EF Core 5. I like it so far but have hit a couple of issues that I am struggling with. This is the one I am really confused about and not understanding.

I have some products represented in a class. I have customers that buy these products in another class. Each customer may call one of my products a different name within their business so I need to allow them to define an alias that they use for my product.

Here are my two parent classes (Products & Customers)

    public class Product
    {
        public int Id {get; set;}
        public Guid IdGuid {get; set;}
        public string Name {get; set;}
    }

    public class Customer
    {
        public int Id {get; set;}
        public Guid IdGuid {get; set;}
        public string Name {get; set;}
    }

In these classes I have an Id column that is used by the Database for referential integrity. I do not pass this Id back to the user. Instead whenever the user gets one of these objects they get the Guid returned so they can uniquely identify the row in the DB. The alias class that joins these two tables is as follows:

    public class ProductCustomerAlias
    {
        public Product Product {get; set;}
        public Customer Customer {get; set;}
        public string Alias {get; set;}
    }

Since I need the database table to use a complex key consisting of the Product Id and the Customer Id I have to override the OnModelCreating method of the context object:

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<ProductCustomerAlias>()
                .HasKey("ProductId", "CustomerId");
        }
'''
This ends up creating a Database table that has the following structure (in Oracle)
'''
CREATE TABLE "RTS"."ProductCustomerAlias" 
(
"ProductId" NUMBER(10,0), 
"CustomerId" NUMBER(10,0), 
"Alias" NVARCHAR2(100)
)

So far so good. I now have an intersect table to store Alias' in that has a primary key of ProductId and CustomerId both being the key integer values from the Products and Customers within the DB context.

So my next step is to start creating the Repo class that retrieves data from these objects. Keep in mind that the end user that submits the request can only pass me the Product.IdGuid and Customer.IdGuid because that is all they ever have. In my Repo I have the following code:

public async Task<ProductCustomerAlias> GetProductCustomerAliasAsync(Guid pProductId, Guid pCustomerId)
{
   var alias = await (from ali in _context.ProductCustomerAlias
                      join prod in _context.Products on ali.Product.Id equals prod.Id
                      join cust in _context.Customers on ali.Customer.Id equals cust.Id
                      where cust.IdGuid == pCustomerId && prod.IdGuid == pProductId
                      select new {Product = prod,
                                  Customer = cust,
                                  Alias = ali.Alias}
                      ).FirstOrDefaultAsync();

   return (IEnumerable<ProductCustomerAlias>)alias;
} 

My problem is that it is giving me the following error:

Cannot convert type '<anonymous type: Models.Product Product, Models.Customer Customer, string Alias>' to 'System.Collections.Generic.IEnumerable<Models.ProductCustomerAlias>' 

Please don't tell me that the error is telling me exactly what is wrong. I am sure it is but if I understood where I was screwing up I would not be wasting my time typing out this ridiculously long explanation. So how can I cast the results from my Linq query to the specified type? Is there something else I am doing wrong? Any help would be greatly appreciated.

CodePudding user response：

Answering your concrete questions.

So how can I cast the results from my Linq query to the specified type?

You can't, because the LINQ query result is anonymous type which is not compatible with the desired result type (concrete entity type), thus cannot be cast to it.

Is there something else I am doing wrong?

Sorry to say that, but basically everything is wrong.

• Select is not needed because the desired result type is the exact type of the entity being queried. i.e. ali variable here

from ali in _context.ProductCustomerAlias

is exactly what you need as a result (after applying the filter and limiting operators)

• Manual joins are also not needed, because they are provided automatically by navigation properties, i.e. here

join prod in _context.Products on ali.Product.Id equals prod.Id

the prod is exactly the same thing as ali.Product

• attempt to cast single object to enumerable is wrong

return (IEnumerable<ProductCustomerAlias>)alias

Even if the alias variable was of correct type, this will fail because it is single object rather than a collection.

So, the solution is quite simple - use the corresponding DbSet, apply filter (Where), limit the result to zero or one (FirstOrDefault{Async}) and you are done.

With one small detail. Since you are querying and returning a full entity, its navigation properties (like Product and Customer) are considered to be a related data, and are not populated (loaded) automatically by EF Core. You have to explicitly opt-in for that, which is called eager loading and explained in the Loading Related Data section of the official EF Core documentation (I would also recommend familiarizing with navigation properties and the whole Relationships concept). With simple words, this requires usage of specifically provided EF Core extension methods called Include and ThenInclude.

With all that being said, the solution is something like this:

public async Task<ProductCustomerAlias> GetProductCustomerAliasAsync(
    Guid pProductId, Guid pCustomerId)
{
    return await _context.ProductCustomerAlias
        .Include(a => a.Product)
        .Include(a => a.Customer)
        .Where(a => a.Customer.IdGuid == pCustomerId && a.Product.IdGuid == pProductId)
        .FirstOrDefaultAsync();
} 

You can even replace the last two lines with single call to the predicate overload of FirstOrDefaultAsync, but that's not essential since it is just a shortcut for the above

return await _context.ProductCustomerAlias
    .Include(a => a.Product)
    .Include(a => a.Customer)
    .FirstOrDefaultAsync(a => a.Customer.IdGuid == pCustomerId && a.Product.IdGuid == pProductId);