Linq-to-SQL - multiple tables

Question

Really basic LINQ question but can someone enlighten me as to how it handles data when it's taken from multiple database tables.

For example if you have a Products tables then using the DBML you get a nice Product object which you can query, update, create etc. (Product.Name, Product.Price etc.) All very nice.

However, if I have a LINQ query that joins Product on a bunch of other tables and brings me back the columns from those tables. There's no 1:1 mapping of DB table to object possible so what is it returned as?

Eg. If it was a combo of Product and Customer how would I query, for example, the customer name:

object.customerName ?

Is it returned as a dataset by default?

It's theoretical at this stage so don't have a query to show. Imagine a simple LINQ to SQL join though, the result of that join is what? Is it a dataset? Datatable? — Full Time Skeleton
– Full Time Skeleton, Commented Apr 18, 2012 at 13:54
I highly recommend LinqPad for testing and developing LINQ queries: linqpad.net. The website has some great tutorials too, as does Microsoft's Linq 101 sample site: code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — Tim Croydon
– Tim Croydon, Commented Apr 18, 2012 at 14:04

Grant Thomas · Accepted Answer · 2012-04-18 13:54:40Z

2

You would generally (that's: often, but not always.) compose anonymous types which your query will return a collection of. You name the properties, say, and can then access them as you would with any other type.

answered Apr 18, 2012 at 13:54

Grant Thomas

45.1k10 gold badges88 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Full Time Skeleton Over a year ago

Ah...ok. I figured it might be something like a custom class....but anonymous types sounds interesting. Not sure I've used them before. Am I right in thinking that if I created a custom class then I'll need properties for all returned values I want access to? Even as I type that I know it's the case....but just making sure!

Full Time Skeleton Over a year ago

Thanks, that's the info I was looking for. Must read more about anonymous types. In your experience would you opt for them over a custom class? Which are faster to create?

mellamokb Over a year ago

Anonymous classes are very good for shaping data and working with it in LINQ. The only time I would use a custom class is if I need to return shaped data from a method and use it in other places, in which case I need a return type like List<MyCustomClass>.

Pleun Over a year ago

+1 Just remember they are only usefull for readonly actions. If you want to update, you need to return the entities themselves

Tim Croydon · Accepted Answer · 2012-04-18 14:01:25Z

1

It creates what's called an 'anonymous type'. You can use it like this:

var x = from p in context.Products
        join o in OrderLines in p.Id = o.ItemId
        select new    // you are creating your anonymous type here
        {
            OrderId = o.Id,
            ProductName = p.Name,
            OrderDate = o.Date
        }

foreach (var y in x)
{
    Console.WriteLine("Product name: " + y.ProductName);
}

answered Apr 18, 2012 at 14:01

Tim Croydon

1,8961 gold badge19 silver badges30 bronze badges

Comments

AD.Net · Accepted Answer · 2012-04-18 13:50:01Z

0

If your keys are set up properly, you should be able to do queries like Product.Customers or the other way around. Or you can join two tables, the syntax is similar to this:

from p in context.Products
join c in context.Customers on p.CId equals c.Id

answered Apr 18, 2012 at 13:50

AD.Net

13.4k2 gold badges30 silver badges47 bronze badges

2 Comments

Full Time Skeleton Over a year ago

Ok, and what would the result be exactly? I mean if I just queried the product table and return a product object that's fine. But what kind of object am I getting back if I join a few tables and the result is an amalgamation of a few tables?

AD.Net Over a year ago

The return type depends on you. You can send anonymous type with new {} or any custom type you want. No need to give -ve for this thing.

Pravin Pawar · Accepted Answer · 2012-04-18 14:00:54Z

0

You would generally have anonymous type or custom object depending upon the select clause of your query

from p in context.Products
join //with other tables with appropriate join conditions
select new
{
   prop1 = p.ProductName,
   prop2 = p.ProductId
}

This would return an anonymous type with 2 properties prop1 & prop2

from p in context.Products
join //with other tables with appropriate join conditions
select new MyProduct()
{
   prop1 = p.ProductName,
   prop2 = p.ProductId
}

This would return custom object called MyProduct & you can set its properties in object initializer's block

answered Apr 18, 2012 at 14:00

Pravin Pawar

2,5793 gold badges34 silver badges40 bronze badges

Comments

Jaider · Accepted Answer · 2012-04-18 14:05:28Z

0

You can create a View in the database and through Linq-to-SQL you can map to an Object.

answered Apr 18, 2012 at 14:05

Jaider

15.1k5 gold badges83 silver badges88 bronze badges

Collectives™ on Stack Overflow

Linq-to-SQL - multiple tables

5 Answers 5

4 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related