Linq to SQL Query Returning Data

Question

I have this method in my Data Service and it is returning two fields DisplayName and Version Key. When I look at the query that is hitting the server it is two select statement? Is there a better way to write this Linq so it only returns the data I need? I am not using toList() so I am not sure why it is returning so much data.

Model Class

 public class AgentDto
    {
        public long Id { get; set; }

        public string Name { get; set; }

        public Guid? VersionKey { get; set; }
    }

Data Service

 public IQueryable<Data.Dto.Agent.AgentDto> GetPublishedAgent()
        {
            var Agent = (from t in UnitOfWork.GetRepository<Template>().Get()
                                join r in UnitOfWork.GetRepository<Regimen>().Get() on t.Id equals r.TemplateId
                                join rp in UnitOfWork.GetRepository<RegimenPart>().Get() on r.Id equals rp.RegimenId
                                join re in UnitOfWork.GetRepository<RegimenEntry>().Get() on rp.Id equals re.RegimenPartId
                                join a in UnitOfWork.GetRepository<Agent>().Get() on re.AgentVersionKey equals a.VersionKey
                                where t.IsCurrentVersion && t.Status == 7 && a.IsCurrentVersion && a.IsActive
                                select new Data.Dto.Agent.AgentDto
                                {
                                 Name = a.DisplayName,
                                 VersionKey= a.VersionKey
                                 });
            Agent = Agent.Distinct();
            return Agent;
        }

Server Profiler

SELECT DISTINCT [t5].[Id], [t5].[Name], [t5].[Added], [t5].[Modified], [t5].[Deleted], [t5].[IsDeleted], [t5].[RxNormId], [t5].[BrandNames], [t5].[IsFreeText], [t5].[AddedBy], [t5].[ModifiedBy], [t5].[DeletedBy], [t5].[RxNormText], [t5].[NccnTallMan], [t5].[RxNormTallMan], [t5].[IsActive], [t5].[VersionKey], [t5].[VersionNumber], [t5].[IsCurrentVersion], [t5].[value] AS [VersionKey2]
FROM (
    SELECT [t4].[Id], [t4].[Name], [t4].[Added], [t4].[Modified], [t4].[Deleted], [t4].[IsDeleted], [t4].[RxNormId], [t4].[BrandNames], [t4].[IsFreeText], [t4].[AddedBy], [t4].[ModifiedBy], [t4].[DeletedBy], [t4].[RxNormText], [t4].[NccnTallMan], [t4].[RxNormTallMan], [t4].[IsActive], [t4].[VersionKey], [t4].[VersionNumber], [t4].[IsCurrentVersion], [t4].[VersionKey] AS [value], [t0].[IsCurrentVersion] AS [IsCurrentVersion2], [t0].[Status]
    FROM [dbo].[Templates] AS [t0]
    INNER JOIN [dbo].[Regimens] AS [t1] ON [t0].[Id] = [t1].[TemplateId]
    INNER JOIN [dbo].[RegimenParts] AS [t2] ON [t1].[Id] = [t2].[RegimenId]
    INNER JOIN [dbo].[RegimenEntries] AS [t3] ON [t2].[Id] = [t3].[RegimenPartId]
    INNER JOIN [dbo].[Agents] AS [t4] ON [t3].[AgentVersionKey] = [t4].[VersionKey]
    ) AS [t5]
WHERE ([t5].[IsCurrentVersion2] = 1) AND ([t5].[Status] = @p0) AND ([t5].[IsCurrentVersion] = 1) AND ([t5].[IsActive] = 1)',N'@p0 int',@p0=7

DisplayName method

  public string DisplayName
        {
            get
            {                
                if (!string.IsNullOrEmpty(this.RxNormTallMan))
                {
                    return this.RxNormTallMan;
                }
                else if (!string.IsNullOrEmpty(this.NccnTallMan))
                {
                    return this.NccnTallMan;
                }

                return this.Name;
            }
        }

Answer 1

The key here is that DisplayName is not a mapped property. I.e. is doesn't correspond with a database field. Therefore, there is no SQL translation for the property. LINQ-to-SQL detects that and decides the best it can do is get all fields of all tables and build the required projection (AgentDto) client-side, i.e. in-memory.

If you want L2S to select only the required fields you have to use only mapped properties in the select statement:

select new Data.Dto.Agent.AgentDto
{
    Name = a.RxNormTallMan.Length > 0 
        ? a.RxNormTallMan 
        : a.NccnTallMan.Length > 0
            ? a.NccnTallMan
            : a.Name,
    VersionKey= a.VersionKey 
});