What class to return from LINQ to SQL classes when using JOIN

Question

I am returning List from my BusinessLogic methods where the Type is one of the classes generated by Linq2Sql classes. Now if I use a join statement and get a result containing properties(columns) from different tables, then my return object should be a List<> of what?

I am using asp.net ajax components from Telerik and set the datasource of, say RadGrid to List.

I am new to using linq2sql classes. Thanks in advance.

Answer 1

You could create a new class which has the properties of what you want, then when you .Select do

List<MyClass> list = ...  .Select(d => new MyClass() { Prop1 = d.P1...etc }).ToList()

Then you can use that list to bind to your controls

Answer 2

This is a generic object, so you could do (when declaring the variable):

List<object> obj = from u in db.Users ...
// or
var obj = from u in db.Users ...

Answer 3

It would be a List of anonymous types. I would create a class with properties that contain the table objects that you're joining instead, and return a List of those.

Answer 4

You could either create a specific model class containing just those properties required by your component or, perhaps better, use an anonymous class.

radGrid.DataSource = joinQuery.Select( j => new { j.Name, j.Price, j.Quantity, .. } );

Answer 5

You can either return an anonymous object:

select x from db.Table1
join y in db.Table2
where x.Table2Id equals y.Table2Id
select new
{
   Value1 = x.Value1,
   Value2 = y.Value2
};

Or roll your own Entity class:

class JoinedTableEntity
{
    public string Value1 { get; set; }
    public string Value2 { get; set; }
}

select x from db.Table1
join y in db.Table2
where x.Table2Id equals y.Table2Id
select new JoinedTableEntity
{
   Value1 = x.Value1,
   Value2 = y.Value2
};

The second solution is more likely to be useful if you're passing the data out of the querying method, as it's difficult to identify the anonymous object, and you won't be able to access its properties without reflection.

If you're just passing the data to a Grid or something which uses reflection to display the data anyway, the anonymous object would likely suffice.

Answer 6

Just create a new class Model to hold the data since it will be a mixture of two classes.

 var exp = (

                from o in this.reposOrders.All()
                join c in this.reposCustomers.All() on o.CustomerID equals c.CustomerID
                where o.OrderID == id
                select new CustomerOrderItem()
                {
                   OrderID= o.OrderID,
                   CustomerID = c.CustomerID,
                   ItemName = o.ItemName,
                   BillingAddress = c.BillingAddress  
                }
            ).Single();
 return exp;




public class CustomerOrderItem(){
   public int OrderItem { get; set;}
   public int CustomerID { get; set;}
   public string ItemName { get; set;}
   public string BillingAddress { get; set;}
}

Answer 7

There are three different options that you need to be aware of in this context:

• Consider using a view or stored procedure. For large sets, or where performance is of the essence, this is the way to go. Import the view or stored procedure and you will have a new type
• Create an anonymous type on the fly. If you need a flat result set with defined members, this is the way to go. In your query, use the new { col1 = abc, col2 = cde, ... } construct to create one. There are limitations, though, as you cannot pass an anonymous type between methods (the type information is not available outside of the local context).
• You don't really need to return the joined object, as it is accessible anyways through the members of your primary object if you use foreign key constraints.