Asked  7 Months ago    Answers:  5   Viewed   19 times

Using the simple example below, what is the best way to return results from multiple tables using Linq to SQL?

Say I have two tables:

Dogs:   Name, Age, BreedId
Breeds: BreedId, BreedName

I want to return all dogs with their BreedName. I should get all dogs using something like this with no problems:

public IQueryable<Dog> GetDogs()
{
    var db = new DogDataContext(ConnectString);
    var result = from d in db.Dogs
                 join b in db.Breeds on d.BreedId equals b.BreedId
                 select d;
    return result;
}

But if I want dogs with breeds and try this I have problems:

public IQueryable<Dog> GetDogsWithBreedNames()
{
    var db = new DogDataContext(ConnectString);
    var result = from d in db.Dogs
                 join b in db.Breeds on d.BreedId equals b.BreedId
                 select new
                        {
                            Name = d.Name,
                            BreedName = b.BreedName
                        };
    return result;
}

Now I realize that the compiler won't let me return a set of anonymous types since it's expecting Dogs, but is there a way to return this without having to create a custom type? Or do I have to create my own class for DogsWithBreedNames and specify that type in the select? Or is there another easier way?

 Answers

24

I tend to go for this pattern:

public class DogWithBreed
{
    public Dog Dog { get; set; }
    public string BreedName  { get; set; }
}

public IQueryable<DogWithBreed> GetDogsWithBreedNames()
{
    var db = new DogDataContext(ConnectString);
    var result = from d in db.Dogs
                 join b in db.Breeds on d.BreedId equals b.BreedId
                 select new DogWithBreed()
                        {
                            Dog = d,
                            BreedName = b.BreedName
                        };
    return result;
}

It means you have an extra class, but it's quick and easy to code, easily extensible, reusable and type-safe.

Tuesday, June 1, 2021
 
Silfverstrom
answered 7 Months ago
80

You can't.

You can only return object, or container of objects, e.g. IEnumerable<object>, IList<object>, etc.

Tuesday, June 8, 2021
 
Shobit
answered 6 Months ago
48

An anonymous type is a real, compiler-generated type that is created for you. The good thing about this is that the compiler can re-use this type later for other operations that require it as it is a POCO.

My understanding of dynamic types is that they are late-bound, meaning that the CLR (or DLR) will evaluate the object at execution time and then use duck typing to allow or disallow member access to the object.

So I guess the difference is that anonymous types are true POCOs that the compiler can see but you can only use and dynamic types are late-bound dynamic objects.

Monday, June 21, 2021
 
akohout
answered 6 Months ago
54

Literal null values are handled differently than parameters which could be null. When you explicitly test against null, the generated SQL will use the IS NULL operator, but when you're using a parameter it will use the standard = operator, meaning that no row will match because in SQL null isn't equal to anything. This is one of the more annoying .NET/SQL semantic mismatches in LINQ to SQL. To work around it, you can use a clause like:

where doc.ParentID == parentID || (doc.ParentID == null && parentID == null)
Monday, August 16, 2021
 
MByD
answered 4 Months ago
47

Error occurs, because you use var and compiler infer query type as IOrderedEnumerable<T> after your first query, and you're trying to assign IEnumerable<T> to it with Skip/Take calls:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<Order>' to 'System.Linq.IOrderedEnumerable<Order>'. An explicit conversion exists (are you missing a cast?)

If you type query as IEnumerable<T> at the beginning everything will work just fine

IEnumerable<Order> query = from m in Orders
                           orderby m.ID descending
                           select m;

query = query.Skip(10).Take(10);
Thursday, November 4, 2021
 
Joel Peltonen
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share