SQL view doesn't return nested objects

Question

I've created SQL View in SQL Server database which returns rows with list of columns:

Id, SupplierName, EmailAddress, PhoneNumber, CsatRating, CsatRatingCount,
Latitude, Longitude, AddressLine1, AddressLine2, AddressLine3, 
City, CountryIsoNumber, StateProvinceCounty, PostCode, 
PartnersOnHoldIds, BusinessClientId, Status, ServiceTypeId, 
StartDT, EndDT, MaxCapacityExceeded, CapabilityId, NetworkId, PartnerI

Now I want to read these data to my object result in this method:

public async Task<IEnumerable<AllocationSupplierViewDto>> GetAllocationSuppliersByIdAsync(Guid id)
{
    var query = $@"
        SELECT *
        FROM vw_SupplierInformation 
        WHERE Id = '{id}'";

    var result = await dbContext.SupplierInformation
        .FromSqlRaw(query)
        .ToListAsync();

    return result;
}

I'm able to see list of rows returned from view but without nested objects. I'm attaching below how my DTOs looks like:

public class AllocationSupplierViewDto
{
    public Guid Id { get; set; }
    public string? SupplierName { get; set; }
    public string? EmailAddress { get; set; }
    public string? PhoneNumber { get; set; }
    public double? CsatRating { get; set; }
    public int? CsatRatingCount { get; set; }
    public double? Latitude { get; set; }
    public double? Longitude { get; set; }
    public string? AddressLine1 { get; set; }
    public string? AddressLine2 { get; set; }
    public string? AddressLine3 { get; set; }
    public string? City { get; set; }
    public string? CountryIsoNumber { get; set; }
    public string? StateProvinceCounty { get; set; }
    public string? Postcode { get; set; }
    public List<Guid>? PartnersOnHoldIds { get; set; }
    public required List<BusinessClientConfigurationViewDto> BusinessClientConfiguration { get; set; }
}

public class BusinessClientConfigurationViewDto
{
    public Guid BusinessClientId { get; set; }
    public int Status { get; set; }
    public List<ServiceViewDto>? Services { get; set; }
    public required AllocationSupplierViewDto AllocationSupplierViewDto { get; set; }
    public Guid AllocationSupplierViewDtoId { get; set; }
}

public class ServiceViewDto
{
    public Guid ServiceTypeId { get; set; }
    public DateTime? StartDT { get; set; }
    public DateTime? EndDT { get; set; }
    public bool MaxCapacityExceeded { get; set; } = false;
    public List<Guid>? CapabilityIds { get; set; }
    public List<NetworkViewDto>? Networks { get; set; }
}

public class NetworkViewDto
{
    public Guid NetworkId { get; set; }
    public List<Guid>? PartnerIds { get; set; }
}

My OnModelCreating method is shown here:

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
        builder.Entity<AllocationSupplierViewDto>().HasKey(e => e.Id);
        builder.Entity<AllocationSupplierViewDto>().ToView("vw_SupplierInformation");
        builder.Entity<AllocationSupplierViewDto>()
            .HasMany(e => e.BusinessClientConfiguration)
            .WithOne(e => e.AllocationSupplierViewDto)
            .HasForeignKey(e => e.AllocationSupplierViewDtoId)
            .IsRequired();
        builder.Entity<BusinessClientConfigurationViewDto>().HasKey(e => e.BusinessClientId);
        builder.Entity<ServiceViewDto>(eb =>
        {
            eb.HasKey(e => e.ServiceTypeId);
        });
        builder.Entity<NetworkViewDto>(eb =>
        {
            eb.HasKey(e => e.NetworkId);
        });
        base.OnModelCreating(builder);
    }

I thought that my OnModelCreating will do everything and at the end all nested objects like BusinessClientConfiguration will have populated data from SQL view base on relationship from there. Where am I making a mistake?

At this moment, BusinessClientConfiguration is null and I do not know why.

I tried to follow documentation: https://learn.microsoft.com/en-us/ef/core/modeling/relationships/one-to-many#required-one-to-many

UPDATE: My DbSet is shown below:

        public DbSet<AllocationSupplierViewDto> SupplierInformation { get; set; }

In general BusinessClientConfiguration doesn't exist in database as a table. It's just DTO to which I would like to add part of data returned from SQL view.

You have a SQL injection issue. You should use parameterization with FromSqlInterpolated and a FormattableString — Charlieface
– Charlieface, Commented Mar 26 at 20:50
Code looks much better but still have the same issue, BusinessClientConfiguration which is just a DTO is null. Need to have there data from SQL view (some part of it). — Robert Daraż
– Robert Daraż, Commented Mar 26 at 22:38
Do you have a proper mapping of SupplierInformation to the view? There should be a ToView somewhere in the configuration. Either way, the problem with the current method is that it returns IEnumerable, so there's no way to compose upon it, or to add Include. — Gert Arnold
– Gert Arnold, Commented Mar 27 at 7:57
@GertArnold I have mapping on OnModelCreating function and looks like this: builder.Entity<AllocationSupplierViewDto>().HasNoKey().ToView("vw_SupplierInformation"); Right now it's not important that my GetAllocationSuppliersByIdAsync return IEnumerable because first I need to have proper data inside of this method. — Robert Daraż
– Robert Daraż, Commented Mar 27 at 8:52
I've just updated question to present you cleaner picture of a problem. — Robert Daraż
– Robert Daraż, Commented Mar 27 at 9:21

Dale K · Accepted Answer · 2025-03-26 21:55:28Z

0

The issue you're running into is the fact that EF will use Lazy-Loading and will not include related entities in your queries by default.

You have two options to handle this:

The most common, will be to modify your query to use .Include() statements that will then Eager-Load the related entity from your context. For example:

var result = await dbContext.SupplierInformation
                            .Include(a => a.BusinessClientConfiguration)
                            .Where(a => a.Id == id)
                            .ToListAsync();

You can also add AutoInclude() to the entities you always want to include in your queries in your OnModelCreating method.

builder.Entity<AllocationSupplierViewDto>().Navigtation(e => e.BusinessClientConfiguration).AutoInclude()

This second method will avoid you having to add the Include() statements in your queries as this will be automagically handled by EF throughout your application.

Check out EF documentation here for more detailed information regarding Eager and Lazy Loading in EF.

edited Mar 26 at 21:55

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Mar 26 at 19:41

akseli

3,0891 gold badge27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Robert Daraż Mar 26 at 22:25

I've tried first approach with no luck. Error message is: Microsoft.Data.SqlClient.SqlException: 'Invalid object name 'SupplierInformation'.' Added to original question how my db set looks like - maybe that's the reason.

Robert Daraż Mar 26 at 22:29

Second approach saying this: Microsoft.Data.SqlClient.SqlException: 'Invalid object name 'BusinessClientConfiguration'.' I do not understand why it's not visible at all this BusinessClientConfiguration.

akseli Mar 27 at 3:55

@RobertDaraż it seems like your DBContext isn't actually 'connected' to your DB in any way. How are you telling EF that your DbSet<AllocationSupplierViewDto> SupplierInformation, for example, is supposed to be reading from vw_SupplierInformation? I think that you've misunderstood how EF works and how it's supposed to map to your DB. Your DBSets have to map 1:1 with your DB entities. Once that's done, you can project your entities into custom DTOs in your code.

Robert Daraż Mar 27 at 8:55

I'm doing this in OnModelCreating method:</br>

builder.Entity<AllocationSupplierViewDto>().HasNoKey().ToView("vw_SupplierInformation");</br> builder.Entity<AllocationSupplierViewDto>().Navigation(e => e.BusinessClientConfiguration).AutoInclude();

and have an error:</br>

Exception: System.InvalidOperationException: Navigation 'AllocationSupplierViewDto.BusinessClientConfiguration' was not found. Please add the navigation to the entity type before configuring it.

Collectives™ on Stack Overflow

SQL view doesn't return nested objects

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related