CODEWITHSUNDEEP
sqllinqlinq-to-sqlstored-procedures
Ash Machine
Ash Machine

Reputation: 9901

How to make a return type for a result set in LINQ

I am having a problem determining how c# and LINQ solve the common problem of handling a data structure that does not necessarily return a table structure, but instead a resultset.

I have a stored procedure that works, and have included it in my DBML

[Function(Name="dbo.p_GetObject")]
public int p_GetObject([Parameter(Name="ObjectType", DbType="NVarChar(200)")] string objectType, [Parameter(Name="ItemState", DbType="Bit")] System.Nullable<bool> itemState, [Parameter(Name="IsPublished", DbType="Bit")] System.Nullable<bool> isPublished)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), objectType, itemState, isPublished);
    return ((int)(result.ReturnValue));
}

The dbml says that the return type is (None) and this could be the crux issue. However I don't have a DBML object that matches the resultset.

The SP takes three parameters, and returns a result set with three columns (ID, Name, Value) with multple rows. I can create a data object for this, and call it resultSet

When I write a function call for this, I get stuck: 

public List<resultset> GetObject(string objectType, bool itemState, bool isPublished)
{
    MyDataContext.p_GetObject(objectType, itemState, isPublished);
}

My questions are:

how do I have the data context call to the stored procedure populate my resultSet object? Is there a better approach? What should the return type be? A SQL view? Looking for good suggestions...

Upvotes: 2

Views: 3812

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062770

If it simply isn't understanding your SP, that could be the SET FMT_ONLY issue... try generating the data from a simplified version of the SP?

Normally, SPs / UDFs that don't map 1:1 with an existing entity would expose themselves in a generated type. You can rename this in the DBML file (not in the designer), but personally I wouldn't; I tend to mark the SP as private, and write my own method that projects into my own POCO type (defined for the repository):

var typed = from row in cxt.SomeFunction(123)
            select new MyType {Id = row.Id, Name = row.Name, ...}

The reason for this is partly for repository purity, and partly to guard against the designer's habit of re-writing the DBML in unexpected ways ;-p See here for more.

Upvotes: 6

Related Questions