CODEWITHSUNDEEP
c#linq-to-sql
Macros
Macros

Reputation: 7119

Linq-to-SQL complex custom object from SP

I have a Linq-to-SQL model that uses stored procs in some places to return the objects where more complex SQL is required. This all works fine.

I now need to return a custom object from an SP that also encapsulates a Linq-to-SQL object. For example, I have a class called Employee based on an Employee table. I also have a custom class called rota defined as follows:

public class rota
{
    public Employee employee{ get; set; }
    public int DisplayOrder { get; set; }
    public DateTime StartingTime { get; set; }
    public DateTime FinishTime { get; set; }
}

I have some fairly complex linq that calculates an employees rota for any given day and then returns that object. I want to move this logic to a stored procedure so I have total control over the sql (the generated sql is not great) however I am not sure how to return that object?

Upvotes: 2

Views: 1445

Answers (2)

Shibbz
Shibbz

Reputation: 249

I would suggest creating a constructor for the Rota object that takes the sproc result as a parameter... that way you can do 

.Select(x => new Rota(x)).ToList<Rota>();

Upvotes: 1

JohnOpincar
JohnOpincar

Reputation: 5823

I believe this will work although I have not run it. If this does not, I know you could do this with a SQL function that returned a table variable.

from x in dataContext.StoredProc("", "", "")
select new Rota {
    DisplayOrder = x.DisplayOrder,
    StartingTime = x.StartingTime,
    FinishTime = x.FinishTime,
    Employee = new Employee {
        EmployeeId = x.EmployeeId,
        Name = x.EmployeeName
    }
}

Upvotes: 1

Related Questions