Linq To Sql return from function as IQueryable

Question

Ok, I have managed to get the following working

public IQueryable getTicketInformation(int ticketID)
{
    var ticketDetails = from tickets in _context.tickets
        join file in _context.file_objects on tickets.ticket_id equals file.source_id
        where tickets.ticket_id == ticketID
        select new { tickets.ticket_id, tickets.title, tickets.care_of_email, file.filename };

    return ticketDetails.AsQueryable();
}

I went ahead and created my own class (myObject) containing the primitives ticket_id, title, care_of_email and filename. Which are the items I am returning in my linq statement.

I modified my statement to be

public IQueryable<myObject> getTicketInformation(int ticketID)
{
    var ticketDetails = from tickets in _context.tickets
        join file in _context.file_objects on tickets.ticket_id equals file.source_id
        where tickets.ticket_id == ticketID
        select new { tickets.ticket_id, tickets.title, tickets.care_of_email, file.filename };

    return ticketDetails.AsQueryable()<myObject>;
}

thinking that this would make it type safe with generics, but I get the error "Cannot convert method group 'AsQueryable' to non-delegate type 'System.Linq.IQueryable'. Did you intend to invoke the method?"

Is what I am trying to do even possible?

Does the myObject class need to implement IEnumerable or IQueryable?

Or is it best to construct the object MyObject from the linq resultset and then just return from the function the object MyObject

public myObject getTicketInformation(int ticketID) 
{

    ....linq statement....
    myObject o = null;

    foreach (obj in linqstatemt)
    {
        o = new myObject();
        o.ticket_id = obj.ticket_id
        .......
    }
    return o;
}

Answer 1

This line is syntactically incorrect:

return ticketDetails.AsQueryable()<myObject>;

and should read

return ticketDetails.AsQueryable<myObject>();

Also, you're creating anonymous objects with the select new {, but you want to create myObject instances. A correct implementation would look like this:

public IQueryable<myObject> getTicketInformation(int ticketID)
{

    return from tickets in _context.tickets
        join file in _context.file_objects on tickets.ticket_id equals file.source_id
        where tickets.ticket_id == ticketID
        select new myObject() { 
            ticket_id = tickets.ticket_id,
            title = tickets.title, 
            care_of_email = tickets.care_of_email, 
            filename = file.filename
        };

}

The new SomeClass() { Property = value, ... syntax creates a SomeClass instance and sets the properties to the given values. Alternatively you could implement a constructor on the myObject class and call it in the linq statement with select new myObject(...).

Answer 2

As Marc stated you're not constructing instances of myObject when your query is run. But additionally you don't need to cast it to an IQueryable<T>, a LINQ select statment will return an IQueryable<T> unless explicity cast to an IEnumerable<T>.

Also, be careful that your DataContext hasn't been disposed of before you try and access the data being returned. But I noticed your context is not constructed in the method, be careful that you're not maintaining a DataContext for too long, it's a unit-of-work object and not meant to be kept open for long periods of time.

Answer 3

Gents, It all makes sense as long as you're only returning single table, but what if there's two or more to be returned???

RPDTDataContext smdt = new RPDTDataContext();
var projectedUsers = smdt.SM_Users.Join(
        smdt.SM_CTSGroups, u => u.CtsGroupID, c => c.id, 
        (u, c) => new { CTSGroup = c.Name, UserName = u.Name, u.EmpID, u.Email });
return projectedUsers;        

Answer 4

You mean:

select new MyObject { TicketId = tickets.ticket_id,
     Title = tickets.title, ...};

(note I tweaked the names slightly to be more C#-idiomatic)

This is an "object initializer" that creates a new MyObject (per record) and assigns the properties from the source data. What you had was an "anonymous type" initializer, which isn't the same. Note that if you have a non-default constructor, you could also use something like:

select new MyObject(tickets.ticket_id, tickets.title);

which uses the specified constructor, passing in the supplied values from the source data.

This will then be IQueryable<MyObject>; you don't need to call .AsQueryable(). Note it would be better for your function to return the typed form (IQueryable<MyObject>) than the untyped IQueryable.