I’m looking for a more efficient way to grab my objects from the database and map them DTOs while using reusable methods.

Here are my database classes

    public class Sign
    {
        public int Id { get; set; }
        public string Name { get; set; } = null!;
    
        public int? SignGroupId { get; set; }
        public SignGroup SignGroup { get; set; } = null!;

        public int LocationId { get; set; }
        public Location Location { get; set; } = null!;

        public static Expression<Func<Sign, SignDTO>> MapToDto()
        {
            return s => new()
            {
                Id = s.Id,
                Name = s.Name ?? "",

    //this call works as I would expect by translating into SQL and executing it at the
    //database level.
                Signs = s.SignGroup.Signs.Where(ss => ss.Id != s.Id)
                    .AsQueryable().Select(MapToGroupSignDto()).ToList(),

    //This call will end up bringing the whole Location object into memory and then
    //perform the mapping from memory.
                LocationInfo = Location.MapToSignLocationDto()
                    .Compile().Invoke(s.Location),
            };
        }

        public static Expression<Func<Sign, GroupSignDTO>> MapToGroupSignDto()
        {
            return gs => new()
            {
                Id = gs.Id,
                Name = gs.Name ?? "",
            };
        }
    }
    
    public class SignGroup
    {
        public int Id { get; set; }
        public List<Sing> Signs { get; set; } = null!;
    }
    
    public class Location
    {
        public int Id { get; set; }
        public string Name { get; set; } = null!;
        public string? Latitude { get; set; }
        public string? Longitude { get; set; }
        public string? State { get; set; }
        public string? City { get; set; }
        public string? Zip { get; set; }
        public string? StreetAddress { get; set; }
        
        public static Expression<Func<Location, SignLocationDTO>> MapToSignLocationDto()
        {
            return l => new()
            {
                Id = l.Id,
                Name = l.Name,
                Latitude = l.Latitude ?? "",
                Longitude = l.Longitude ?? "",
            };
        }
    }

Here are my DTO Objects:

 public record SignDTO
    {
        public int Id { get; set; }
        public string Name { get; set; } = null!;
    
        public int SignGroupId { get; set; }
        [Comment("Signs At Same Location")]
        public List<GroupSignDTO> AdditionalSings { get; set; } = null!;
    
        public SignLocationDTO LocationInfo { get; set; } = null!;
    }
    
    public record GroupSignDTO
    {
        public int Id { get; set; }
        public string Name { get; set; } = null!;
    }
    
    public record LocationDTO
    {
        public int Id { get; set; }
        public string Name { get; set; } = null!;
        public string Latitude { get; set; } = null!;
        public string Longitude { get; set; } = null!;
    }

Here are my series of functions calls as they exist now. I know the expression functions are repeated in their above classes, but I wanted to make it easier to follow.

public async Task<List<SignDTO>> GetListDataAsync()
    {
        IQueryable<Sign> signIQ = _context.Signs;
        Queryable<SignDTO> dtoIQ = signIQ.Select(Sign.MapToDto());
    
        return await dtoIQ.ToListAsync();
    }
    
    public static Expression<Func<Sign, SignDTO>> MapToDto()
    {
        return s => new()
        {
            Id = s.Id,
            Name = s.Name ?? "",
    
    //this call works as I would expect by translating into SQL and executing it at the
    //database level.
            Signs = s.SignGroup.Signs.Where(ss => ss.Id != s.Id)
                .AsQueryable().Select(MapToGroupSignDto()).ToList(),
    
    //This call will end up bringing the whole Location object into memory and then
    //perform the mapping from memory.
            LocationInfo = Location.MapToSignLocationDto()
                .Compile().Invoke(s.Location),
        };
    }
    
    public static Expression<Func<Sign, GroupSignDTO>> MapToGroupSignDto()
    {
        return gs => new()
        {
            Id = gs.Id,
            Name = gs.Name ?? "",
        };
    }
    
    public static Expression<Func<Location, SignLocationDTO>> MapToSignLocationDto()
    {
        return l => new()
        {
            Id = l.Id,
            Name = l.Name,
            Latitude = l.Latitude ?? "",
            Longitude = l.Longitude ?? "",
        };
    }

What I’m hoping to find, if possible, is a simple but also better way to grab the LocationInfo that would put the whole call into SQL and allow the database to only return what I really need without causing the SQL to take for ever to execute. I don’t like the idea of the database returning all the info related to the Location and then the server using memory to pair it down to what I really need.

I have tried various different ways to do this, but the only way that has accomplished what I wanted was to put all the code into a single Expression function like:

public static Expression<Func<Sign, SignDTO>> MapToDto()
    {
        return s => new()
        {
            Id = s.Id,
            Name = s.Name ?? "",

            Signs = s.SignGroup.Signs.Where(ss => ss.Id != s.Id)
                .Select(gs => new()
                {
                    Id = gs.Id,
                    Name = gs.Name ?? "",
                ).ToList(),

            LocationInfo = new()
            {
                Id = s.Location.Id,
                Name = s.Location.Name,
                Latitude = s.Location.Latitude ?? "",
                Longitude = s.Location.Longitude ?? "",
            },
        };
    }

My issue with this is that the code is not reusable in other areas and in order to maintain any future changes I’d have to go to multiple places.

Any help and ideas in this area would be greatly appreciated.
Thank you 🙂