Linq calling a method in select statement very slow

Question

I am trying to find if there is a way to call a method from within my linq select statement to build a list of objects that doesn't dramatically slow it down. The reason behind this is that I also want to call the same method when trying to get only one of the objects and do not want to have to maintain both versions (i.e. if I have another field added to the object or want to render one of the fields differently I will not have to change it in multiple places).

In the example below, TEST 1 runs over 100 times faster than TEST 2:

// Start timer
var timer = new Stopwatch();
timer.Start();

var test = (from job in dc.Jobs
        where !job.archived
        select new JobExtended()
        {
            JobId = job.jobId,
            NodeId = job.nodeId,
            JobName = job.name != string.Empty ? job.name : "TBC",
            Salary = job.salary,
            RecruiterId = job.fkRecruiterId,
            RecruiterNodeId = job.JobRecruiter != null ? job.JobRecruiter.recruiterNodeId : null,
            RecruiterName = job.JobRecruiter != null ? job.JobRecruiter.name : string.Empty,
            LocationId = job.fkLocationId,
            Location = job.refJobLocation != null ? job.refJobLocation.jobLocation : "",
            ContractTypeId = job.fkContractTypeId,
            ContractType = job.refJobContractType != null ? job.refJobContractType.contractType : "",
            CategoryId = job.fkCategoryId,
            Category = job.refJobCategory != null ? job.refJobCategory.category : "",
            ClosingDate = job.closingDate,
            Featured = job.featured,
            JobOfTheWeek = job.jobOfTheWeek,
            PublishedDate = job.publishedDate,
            Url = "/jobs/" + job.name.Replace(" ", "-").Replace("&", "and").Replace("'", "") + (job.fkLocationId.HasValue ? "-in-" + job.refJobLocation.jobLocation.Replace(" ", "-").Replace("&", "and").Replace("'", "") : "") + "-jn" + job.jobId,
            CreatedOn = job.createdOnDate,
            PrintWidth = job.printWidth,
            PrintHeight = job.printHeight,
            UntilFilled = (job.untilFilled != null && job.untilFilled.Value),
            AdvertCost = job.advertCost,
            DatesToShow = job.relJobDates.Where(x => x.fkJobId == job.jobId).Select(x => x.date).ToList(),
            IsParentJob = job.relLinkedJobs != null && job.relLinkedJobs.Any(x => x.fkParentJobId == job.jobId),
            IsAlternateWeekJob = job.alternateWeek != null && job.alternateWeek.Value,
            Archived = job.archived,
            LastModifiedDate = job.lastModifiedDate,
            RecruiterContactDetails = job.recruiterContactDetails
        }).ToList();

// Stop timer
timer.Stop();

// Output info
litTest.Text = "TEST 1 in " + timer.Elapsed.TotalSeconds + " seconds<br/>";

//Start timer
timer = new Stopwatch();
timer.Start();

var test2 = (from job in dc.Jobs
            where !job.archived
            select GetJobDetails(job)).ToList();

//Stop timer
timer.Stop();

//Output info
litTest.Text += "TEST 2 in " + timer.Elapsed.TotalSeconds + " seconds<br/>";

This is the method that TEST 2 is calling which should be creating the same object that is being returned in TEST 1:

public static JobExtended GetJobDetails(Data.Job job)
{
    return new JobExtended()
    {
        JobId = job.jobId,
        NodeId = job.nodeId,
        JobName = job.name != string.Empty ? job.name : "TBC",
        Salary = job.salary,
        RecruiterId = job.fkRecruiterId,
        RecruiterNodeId = job.JobRecruiter != null ? job.JobRecruiter.recruiterNodeId : null,
        RecruiterName = job.JobRecruiter != null ? job.JobRecruiter.name : string.Empty,
        LocationId = job.fkLocationId,
        Location = job.refJobLocation != null ? job.refJobLocation.jobLocation : "",
        ContractTypeId = job.fkContractTypeId,
        ContractType = job.refJobContractType != null ? job.refJobContractType.contractType : "",
        CategoryId = job.fkCategoryId,
        Category = job.refJobCategory != null ? job.refJobCategory.category : "",
        ClosingDate = job.closingDate,
        Featured = job.featured,
        JobOfTheWeek = job.jobOfTheWeek,
        PublishedDate = job.publishedDate,
        Url = "/jobs/" + job.name.Replace(" ", "-").Replace("&", "and").Replace("'", "") + (job.fkLocationId.HasValue ? "-in-" + job.refJobLocation.jobLocation.Replace(" ", "-").Replace("&", "and").Replace("'", "") : "") + "-jn" + job.jobId,
        CreatedOn = job.createdOnDate,
        PrintWidth = job.printWidth,
        PrintHeight = job.printHeight,
        UntilFilled = (job.untilFilled != null && job.untilFilled.Value),
        AdvertCost = job.advertCost,
        DatesToShow = job.relJobDates.Where(x => x.fkJobId == job.jobId).Select(x => x.date).ToList(),
        IsParentJob = job.relLinkedJobs != null && job.relLinkedJobs.Any(x => x.fkParentJobId == job.jobId),
        IsAlternateWeekJob = job.alternateWeek != null && job.alternateWeek.Value,
        Archived = job.archived,
        LastModifiedDate = job.lastModifiedDate,
        RecruiterContactDetails = job.recruiterContactDetails
    };
}

The reason for this is because I want to be able to call "GetJobDetails" for returning a single job for example:

    public JobExtended GetJobDetails(int jobId)
    {
        using (DataContext dc = new DataContext())
        {
            return dc.Jobs.Where(x => x.jobId == jobId).Select(j => GetJobDetails(j)).FirstOrDefault();
        }
    }

Doing it like this would allow me to only ever have to update the "GetJobDetails" method if for example I decided to add a new field of change how the "Url" value was generated but doing it this way is a lot slower. Is there a way around this, I have already tried the following which do not seem to help:

var test3 = (from job in dc.Jobs
                where !job.archived
                select job).AsEnumerable()
                .Select(GetJobDetails).ToList();

var test4 = (from job in dc.Jobs
                where !job.archived
                select GetJobDetails(job));
var test4a = test4.ToList();

Answer 1

The reason TEST 1 is faster because the query is executed once on the server and only returns the selected fields.

var test = (from job in dc.Jobs
    where !job.archived
    select new JobExtended()
    {
        JobId = job.jobId,
        NodeId = job.nodeId,
        ...
    }).ToList();

When you call GetJobDetails in TEST 2 the parameter j needs to be materialized first before it can be send as parameter to GetJobDetails. And so there are multiple calls of the full objects.

return dc.Jobs.Where(x => x.jobId == jobId).Select(j => GetJobDetails(j)).FirstOrDefault();

In order to achieve something like you want you should use extension methods. This one extends IQueryable.

    public static IEnumerable<JobExtended> SelectJobExtended(this IQueryable<Data.Job> query)
    {
        return query
            .Select(o => new JobExtended()
            {
                JobId = job.jobId,
                NodeId = job.nodeId,
                ...
            }
    }

Then you can call:

dc.Jobs.Where(x => x.jobId == jobId).SelectJobExtended().FirstOrDefault();

Answer 2

I've seen this kind of issue before. If I recall, what we did was "stacked" the queries.

public IEnumerable<JobExtended> ConvertToJobExtended(IEnumerable<Job> jobs)
{
    return
        from job in jobs
        select new JobExtended()
        {
            MyString = job.MyInt.ToString(),
            ...
        };
}

Then what you can do is call it the following way:

var query = (from job in dc.Jobs
        where !job.archived
        select job;

var test2 = ConvertToJobExtended(query).ToList();

There are plenty of alternatives that can go from here... I hope this goes in the right direction of what you're looking to do.