Get value in 1-to-many relationship (EF Core)

asp.net-core entity-framework-core

Question

I'm seeking an equivalent to this SQL query in EF Core:

SELECT Id, Name, (Select AdminRoleId From EventAdmins Where EventId = Events.Id And AdminId = [value from cookie]) As EventRoleId From Events

This is what I have so far:

public IList<Event> Events { get; set; }
public IList<EventAdmin> EventAdmins { get; set; }

public async Task<IActionResult> OnGetAsync() {

    var adminId = Guid.Parse(Request.Cookies["Adm_AdminId"]);

    Events = await _context.Events.SelectMany(e => e.EventAdmins.Where(x => x.EventId == e.Id && x.AdminId == adminId).Select(x => x.AdminRoleId)).AsNoTracking().ToListAsync();

    return Page();
}

I'm not sure what's wrong, but I get an error saying "Error CS0452: The type 'Guid' must be a reference type in order to use it as parameter 'TEntity' in the generic type or method".

Event model:

public class Event {

    public Guid Id { get; set; }
    public string Name { get; set; }

    [ForeignKey("Id")]
    public IList<EventAdmin> EventAdmins { get; set; }

}

EventAdmin model:

public class EventAdmin {

    public Guid Id { get; set; }
    public Guid EventId { get; set; }
    public Guid AdminId { get; set; }
    public Guid AdminRoleId { get; set; }

    [ForeignKey("EventId")]
    public Event Events { get; set; }

}
1
1
12/17/2018 7:43:48 AM

Accepted Answer

Reason

This error occurs because you're trying to ask EF Core not to track a list of GUID. However, the list of Guid is a list of value types.

As you know, EF Core can only track a series of reference type, so the method signature of AsNoTracking<TEntity>() is :

public static IQueryable<TEntity> AsNoTracking<TEntity> (this IQueryable<TEntity> source) 
    where TEntity : class;

Note the constraints of where TEntity : class.

In other words, you can never invoke AsNoTracking<Guid>():

Events = await _context.Events
    .SelectMany(e => e.EventAdmins.Where(x => x.EventId == e.Id).Select(x => x.AdminRoleId))
    .AsNoTracking()    // Actually, it will invoke `AsNoTracking<Guid>()`
    .ToListAsync();

How to Fix

Your SQL doesn't seem valid. I guess you want to return a {Id, Name, EventRoleId}.

If you would like to do that with SelectMany, you could simply query as below:

var Events = await this._context.Events
    .SelectMany( 
        e => e.EventAdmins.Where(x => x.EventId == e.Id).Select(x => x.AdminRoleId),
        (p,g) => new {
            Id = p.Id,
            Name = p.Name,
            EventRoleId = g
        }
    )
    // .AsNoTracking()   
    .ToListAsync();

There's no need to call .AsNoTracking() at all. Because no tracking is performed if the result set does not contain any entity types.


As a side note, you shouldn't decorate the Event.EventAdmins with a [ForeignKey("Id")] attribute :

    public class Event {

        public Guid Id { get; set; }
        public string Name { get; set; }

        [ForeignKey("Id")]
        public IList EventAdmins { get; set; }

    }

Because the Event is the principal entity and the EventAdmin is the dependent entity. Only the EventAdmin has a foreign key that references the Event.

1
12/17/2018 7:03:17 AM

Popular Answer

Your LINQ query doesn't seem to include adminId so I can't see how it could work. Try something like this:

var eventAdmin = _context.EventAdmin.SingleOrDefault( e => e.Id == adminId);

var events = eventAdmin.Events;

Try using a tool like Linqpad to dissect your queries one step at a time.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow