Entity Framework - Query only returning one result

user_4406072 0
Viewed 210

I have an app with three entities: events, eventBands, bands. I've written the following query in a controller class but it only seems to be returning one result. I'd like it to return all the bands linked to that event in the database.

// EventController.cs (Edit method)


    var viewModel = new EventUpdateData();
                    Event et = await _context.Event.Where(e => e.EventId == id)
                                                   .Include(b => b.EventBands)                                               
                                                   .FirstOrDefaultAsync();
    
                    viewModel.Event = et;
                    viewModel.EventBand = evtBand;
                    viewModel.Bands = et.EventBands.Select(b => b.Band).ToList();
    
                    return View(viewModel);

// EventUpdateData (ViewModel)

    public class EventUpdateData
    {
        public Event Event { get; set; }

        public EventBand EventBand { get; set; }

        public IEnumerable <Band> Bands { get; set; }
        
    }

// Edit.cshtml (list of all bands in database for select field)

            <div class="col-sm-2">
                <label for="band-list">Bands</label>
            </div>
            <div class="col-sm-5">
                <select name="band-list" id="band-list" class="band-list form-control">
                    <option value="#">--Please select a band--</option>
                    @if (Model.Bands != null)
                    {
                        @foreach (var band in Model.Bands)
                        {
                            <option value="@band.BandId">@band.BandTitle</option>
                        }
                    }
                </select>
            </div>

// Edit.cshtml (list of all bands related to an event)


    <tbody class="bands">
    
                                    @foreach (var band in Model.Bands)
                                    {                                    
                                        var bandHours = Int32.Parse(band.BandHourlyRate) * Model.EventBand.EventBandHours;
                                    
                                        <tr class="event-band" data-bandID="@band.BandId">
                                            <td>
                                                <a href="#">@band.BandTitle</a>&nbsp;&nbsp;
                                            </td>
                                            <td class="band-rate" data-val="@band.BandHourlyRate">
                                                @bandHours
                                            </td>
                                        </tr>
                                    }
                                </tbody>

// dbo.Band (database)

   band_id band_title   band_contact band_phone band_rate event_id
    1   Green Fields    Mike Ellery 07110291029 100 1
    2   House Chicks    Dave Hart   07111928193 200 1
    3   Groove Surfaces Marie Penn  07566910295 150 1

( I would like the query to get all of these bands).

// dbo.EventBand

   EventBandId EventId BandId EventBandHourlyRate
    54  1   2   1
    55  1   1   2
    56  1   1   2
    57  1   2   1
    58  1   3   3
    59  1   1   1
    60  1   1   1
    61  1   2   2
    62  1   2   2
    63  1   1   1
    64  1   2   1

// dbo.Event (database)

1   Jenny's Garden Party 14/05/2021 00:00:00    Jenny Wren  The Grove
2   Bob's Balloon Party  15/06/2021 00:00:00    Bob The Garden

// Event.cs

public class Event
{
    public int EventId { get; set; }
    public string EventTitle { get; set; }

    [DataType(DataType.Date)]
    public DateTime EventDate { get; set; }
    public string EventCustomer { get; set; }
    public string EventVenue { get; set; }
    public virtual ICollection<Band> Band { get; set; }
    public virtual ICollection<EventBand> EventBand { get; set; }
    public virtual ICollection<EventCaterer> Caterer { get; set; }
}

// EventBand.cs

public class EventBand
{
    public int EventBandId { get; set; }

    public int EventBandHours { get; set; }

    public int EventId { get; set; }

    public int BandId { get; set; }

    public virtual Event Event { get; set; }

    public virtual Band Band { get; set; }
}

// Band.cs

public class Band
{
    public int BandId { get; set; }

    public string BandTitle { get; set; }

    public string BandContact { get; set; }

    public string BandPhone { get; set; }

    public string BandHourlyRate { get; set; }

    public virtual ICollection<EventBand> EventBand { get; set; }

}
entity-framework .net c#
1 Answers

After having a closer look at your example code, I think your mapping may be getting confused between the relationships of EventBands and Bands. Why do you have both a many-to-many (Event.EventBands) and a one-to-many? (Event.Bands)

EF can express many-to-many relationships two ways depending on the use of the joining table. Since you want to track hours in the joining table, the joining entity is required and it forms more of a One-to-Many-to-One relationship. An Event has Many EventBands, which each have One Band. Having both a collection of EventBands and Bands will likely cause some confusion if relying on EF's default convention-based mapping. For an Event to have a collection of Bands, it will expect Band to have an EventId. It doesn't, so EF must be mapping the relationship somehow, or it's boiling into EventBand.

With the explicit EventBand entity with an EventBandId and extra properties, you will want to set up your mapping:

public class Event
{
    [Key]
    public int EventId { get; set; }
    // ...

    // public virtual ICollection<Band> Bands { get; set; } <- Remove.
    public virtual ICollection<EventBand> EventBands { get; set; } = new List<EventBand>();
}

public class Band
{
    [Key]
    public int BandId { get; set; }
    // ...

    public virtual ICollection<EventBand> EventBands { get; set; } = new List<EventBand>();
}

public class EventBand
{
    [Key]
    public int EventBandId { get; set; }
    [ForeignKey("Event")]
    public int EventId { get; set; }
    [ForeignKey("Band")]
    public int BandId { get; set; }
    
    public int EventBandHours { get; set; }
    public virtual Event Event { get; set; }
    public virtual Band Band { get; set; }
}

EF should be able to resolve this mapping without too much trouble, but it doesn't hurt to be explicit. From the Event and Band ends:

// Event config.
HasMany(x => x.EventBands)
    .WithRequired(x => x.Event); // EF 6
    //.WithOne(x => x.Event).Required(); // EF Core

// Band config.
HasMany(x => x.EventBands)
    .WithRequired(x => x.Band); // EF 6
    //.WithOne(x => x.Band).Required(); // EF Core

Or, from the EventBand config:

HasRequired(x => x.Event)
    .WithMany(x => x.EventBands); 

HaRequired(x => x.Band)
    .WithMany(x => x.EventBands);

Avoid configure relationships from both ends, it can cause issues. Either map it from the Event & Band -> EventBand, or from the EventBand -> Event & Band, not both.

Update: Example entities, mappings and queries for relationship in EF 6...

Entities:

public class Event
{
    public int EventId { get; set; }
    public string Name { get; set; }

    public ICollection<EventBand> EventBands { get; set; } = new List<EventBand>();
}

public class Band
{
    public int BandId { get; set; }
    public string Name { get; set; }

    public ICollection<EventBand> EventBands { get; set; } = new List<EventBand>();
}


public class EventBand
{
    public int EventBandId { get; set; }
    public int Rank { get; set; }

    public virtual Event Event { get; set; }
    public virtual Band Band { get; set; }
}

Relationship Configuration:

public class EventConfiguration : EntityTypeConfiguration<Event>
{
    public EventConfiguration()
    {
        ToTable("Events");
        HasKey(x => x.EventId)
            .Property(x => x.EventId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        HasMany(x => x.EventBands)
            .WithRequired(x => x.Event)
            .Map(x => x.MapKey("EventId"));
    }
}

public class BandConfiguration : EntityTypeConfiguration<Band>
{
    public BandConfiguration()
    {
        ToTable("Bands");
        HasKey(x => x.BandId)
            .Property(x => x.BandId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        HasMany(x => x.EventBands)
            .WithRequired(x => x.Band)
            .Map(x => x.MapKey("BandId"));
    }
}

Query:

[Test]
public void TestEventBandEagerLoading()
{
    using (var context = new TestDbContext())
    {
        context.Configuration.LazyLoadingEnabled = false;
        var bands = context.Events
            .Include(x => x.EventBands)
            .Include(x => x.EventBands.Select(eb => eb.Band))
            .ToList();
    }
}
  • Disabled lazy loading to prove the data was eager loaded.
user_4234970
Related