Return anonymous type results?

Question

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?

Indonesian

Just out of curiosity, why do all the Linq examples show using anonymous types, if they don't work. Eg, this example does foreach (var cust in query) Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City); – Belinda

@Hot Licks - the Customer table in those examples is an entity represented by a class. The example just doesn't appear to show the definitions of those classes. – Indonesian

Nor does it tell you that a compiler swizzle is replacing "var" with the class name. – Belinda

Answer 1

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.

Sunup

I like this approach but now I'm not sure how to display the dog's name. If I'm binding the result to a DataGrid, can I get the properties from Dog without defining them explicitly in the DogWithBreed class or do I have to create the getter/setter for each field that I want to display? – Indonesian

Do DataGrids not allow you to specify the property as "Dog.Name"? I forget now why I hate them enough never to use them... – Sunup

@JonathanS. how u did this in template column ?please tell me i am in similar situation – Kwok

Hey, I like this method of encapsulating the two classes into one. It makes it easy to work with. Not to mention creating simple classes to be used only in the current context do make it cleaner. – Urology

@Kwok Usually in the RowDataBound handler. You could also: 1. Add properties to DogWithBreed to access the properties in Dog you need. 2. Define DogWithBreed without Dog but add the properties you need from Dog. 3. DogWithBreed could inherit dog (I like this option since you get all the dog properties already, though you have to manually set them in the query). 4. Extend Dog class to have a place for Breed Name (NOT RECOMMENDED). – Nerva

This really doesn't answer the question that the OP had, "is there a way to return this without having to create a custom type"? – Gerrit

@teedyay, where would you put that extra class? – Streetwalker

what if after I got the result from linq sql, I used again lambda expression to join it to another DbSet? is it going to work? – Stowell

Answer 2

You can return anonymous types, but it really isn't pretty.

In this case I think it would be far better to create the appropriate type. If it's only going to be used from within the type containing the method, make it a nested type.

Personally I'd like C# to get "named anonymous types" - i.e. the same behaviour as anonymous types, but with names and property declarations, but that's it.

EDIT: Others are suggesting returning dogs, and then accessing the breed name via a property path etc. That's a perfectly reasonable approach, but IME it leads to situations where you've done a query in a particular way because of the data you want to use - and that meta-information is lost when you just return IEnumerable<Dog> - the query may be expecting you to use (say) Breed rather than Ownerdue to some load options etc, but if you forget that and start using other properties, your app may work but not as efficiently as you'd originally envisaged. Of course, I could be talking rubbish, or over-optimising, etc...

Demineralize

Hey, I'm not one to not want features because of fear out of the way they'll be abused, but can you imagine the kinds of crufty code that we'd see if they allowed named anonymous types to be passed out? (shiver) – Ashe

We might see some abuse. We might also see some much simpler code where we just want a tuple, basically. Not everything needs to be an object with complex behaviour. Sometimes "just the data" is the Right Thing. IMO, of course. – Demineralize

Thanks, so your preference is to create types even if it's for a one-off view such as this? I have a lot of reports that slice the same data in different ways and was hoping to not have to create all of these different types (DogsWithBreeds, DogsWithOwnerNames, etc.) – Indonesian

I'd try not to need to slice it in quite so many ways, or put the slicing part in the place which needs the data so you can use anonymous types - but beyond that, yes. It sucks in some ways, but such is life I'm afraid :( – Demineralize

Time has passed since this question was answered and now we have records public record DogWithBreed(Dog Dog, Breed Breed); and tuples (Dog dog, Breed breed); both providing very short declarations. – Linden

Answer 3

Just to add my two cents' worth :-) I recently learned a way of handling anonymous objects. It can only be used when targeting the .NET 4 framework and that only when adding a reference to System.Web.dll but then it's quite simple:

...
using System.Web.Routing;
...

class Program
{
    static void Main(string[] args)
    {

        object anonymous = CallMethodThatReturnsObjectOfAnonymousType();
        //WHAT DO I DO WITH THIS?
        //I know! I'll use a RouteValueDictionary from System.Web.dll
        RouteValueDictionary rvd = new RouteValueDictionary(anonymous);
        Console.WriteLine("Hello, my name is {0} and I am a {1}", rvd["Name"], rvd["Occupation"]);
    }

    private static object CallMethodThatReturnsObjectOfAnonymousType()
    {
        return new { Id = 1, Name = "Peter Perhac", Occupation = "Software Developer" };
    }
}

In order to be able to add a reference to System.Web.dll you'll have to follow rushonerok's advice : Make sure your [project's] target framework is ".NET Framework 4" not ".NET Framework 4 Client Profile".

Supersedure

ASP.NET Mvc School ;) – Edh

Answer 4

In C# 7 you can now use tuples!... which eliminates the need to create a class just to return the result.

Here is a sample code:

public List<(string Name, string BreedName)> 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
             }.ToList();

    return result.Select(r => (r.Name, r.BreedName)).ToList();
}

You might need to install System.ValueTuple nuget package though.

Electrify

You don't need ToList() as that forces a query evaluation and allocates the list in memory. Just use IEnumerable/IQueryable as the return type and there is no need for ToList() at all. I like using tuples so from c# 7 you can simply return a tuple: IQueryable<(string Name, string BreedName)>, then in the linq query select (d.Name, b.BreedName). Then you don't need the multiple selects. – Lyra

Answer 5

You must use ToList() method first to take rows from database and then select items as a class. Try this:

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

List<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
                  }).ToList()
                    .Select(x=> 
                          new Dog{
                              Name = x.Name,
                              BreedName = x.BreedName,
                          }).ToList();
return result;}

So, the trick is first ToList(). It is immediately makes the query and gets the data from database. Second trick is Selecting items and using object initializer to generate new objects with items loaded.

Hope this helps.

Kellikellia

Answer 6

You could do something like this:

public System.Collections.IEnumerable 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.ToList();
}

Gerrit

Answer 7

No you cannot return anonymous types without going through some trickery.

If you were not using C#, what you would be looking for (returning multiple data without a concrete type) is called a Tuple.

There are alot of C# tuple implementations, using the one shown here, your code would work like this.

public IEnumerable<Tuple<Dog,Breed>> 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 Tuple<Dog,Breed>(d, b);

    return result;
}

And on the calling site:

void main() {
    IEnumerable<Tuple<Dog,Breed>> dogs = GetDogsWithBreedNames();
    foreach(Tuple<Dog,Breed> tdog in dogs)
    {
        Console.WriteLine("Dog {0} {1}", tdog.param1.Name, tdog.param2.BreedName);
    }
}

Immateriality

This does not work. Throws a NotSupportedException: Only parameterless constructors and initializers are supported in LINQ to Entities – Tiffinytiffy

True, the Tuple class does not have a default constructor and will not work correctly with LINQ to Entities in this way. It does however work fine with LINQ to SQL as in the question. I haven't tried this, but it may work with ... select Tuple.Create(d, b). – Immateriality

Since Tuples aren't supported by some LINQ providers, couldn't you select an anonymous type, convert it to IEnumerable, then select a tuple from that? – Scripture

Answer 8

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?

Use use object to return a list of Anonymous types without creating a custom type. This will work without the compiler error (in .net 4.0). I returned the list to the client and then parsed it on JavaScript:

public object 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;
}

Inheritance

In my opinion it would be more correct and readable if your method signature looked like this: public IEnumerable<object> GetDogsWithBreedNames() – Treadmill

Answer 9

This doesn't exactly answer your question, but Google led me here based on the keywords. This is how you might query an anonymous type from a list:

var anon = model.MyType.Select(x => new { x.Item1, x.Item2});

Cirenaica

Answer 10

Just select dogs, then use dog.Breed.BreedName, this should work fine.

If you have a lot of dogs, use DataLoadOptions.LoadWith to reduce the number of db calls.

Pavlodar

Answer 11

You can not return anonymous types directly, but you can loop them through your generic method. So do most of LINQ extension methods. There is no magic in there, while it looks like it they would return anonymous types. If parameter is anonymous result can also be anonymous.

var result = Repeat(new { Name = "Foo Bar", Age = 100 }, 10);

private static IEnumerable<TResult> Repeat<TResult>(TResult element, int count)
{
    for(int i=0; i<count; i++)
    {
        yield return element;
    }
}

Below an example based on code from original question:

var result = GetDogsWithBreedNames((Name, BreedName) => new {Name, BreedName });


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

Grease

Answer 12

Try this to get dynamic data. You can convert code for List<>

public object 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.FirstOrDefault();
}

dynamic dogInfo=GetDogsWithBreedNames();
var name = dogInfo.GetType().GetProperty("Name").GetValue(dogInfo, null);
var breedName = dogInfo.GetType().GetProperty("BreedName").GetValue(dogInfo, null);

Manicotti

Answer 13

Well, if you're returning Dogs, you'd do:

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

If you want the Breed eager-loaded and not lazy-loaded, just use the appropriate DataLoadOptions construct.

Ashe

Answer 14

If you have a relationship setup in your database with a foriegn key restraint on BreedId don't you get that already?

So I can now call:

internal Album GetAlbum(int albumId)
{
    return Albums.SingleOrDefault(a => a.AlbumID == albumId);
}

And in the code that calls that:

var album = GetAlbum(1);

foreach (Photo photo in album.Photos)
{
    [...]
}

So in your instance you'd be calling something like dog.Breed.BreedName - as I said, this relies on your database being set up with these relationships.

As others have mentioned, the DataLoadOptions will help reduce the database calls if that's an issue.

Brotherton

Answer 15

BreedId in the Dog table is obviously a foreign key to the corresponding row in the Breed table. If you've got your database set up properly, LINQ to SQL should automatically create an association between the two tables. The resulting Dog class will have a Breed property, and the Breed class should have a Dogs collection. Setting it up this way, you can still return IEnumerable<Dog>, which is an object that includes the breed property. The only caveat is that you need to preload the breed object along with dog objects in the query so they can be accessed after the data context has been disposed, and (as another poster has suggested) execute a method on the collection that will cause the query to be performed immediately (ToArray in this case):

public IEnumerable<Dog> GetDogs()
{
    using (var db = new DogDataContext(ConnectString))
    {
        db.LoadOptions.LoadWith<Dog>(i => i.Breed);
        return db.Dogs.ToArray();
    }

}

It is then trivial to access the breed for each dog:

foreach (var dog in GetDogs())
{
    Console.WriteLine("Dog's Name: {0}", dog.Name);
    Console.WriteLine("Dog's Breed: {0}", dog.Breed.Name);        
}

Haar

Answer 16

If the main idea is to make the SQL select statement sent to the Database server have only the required fields, and not all the Entity fields, then u can do this:

public class Class1
{
    public IList<Car> getCarsByProjectionOnSmallNumberOfProperties()
    {

        try
        {
            //Get the SQL Context:
            CompanyPossessionsDAL.POCOContext.CompanyPossessionsContext dbContext 
                = new CompanyPossessionsDAL.POCOContext.CompanyPossessionsContext();

            //Specify the Context of your main entity e.g. Car:
            var oDBQuery = dbContext.Set<Car>();

            //Project on some of its fields, so the created select statment that is
            // sent to the database server, will have only the required fields By making a new anonymouse type
            var queryProjectedOnSmallSetOfProperties 
                = from x in oDBQuery
                    select new
                    {
                        x.carNo,
                        x.eName,
                        x.aName
                    };

            //Convert the anonymouse type back to the main entity e.g. Car
            var queryConvertAnonymousToOriginal 
                = from x in queryProjectedOnSmallSetOfProperties
                    select new Car
                    {
                        carNo = x.carNo,
                        eName = x.eName,
                        aName = x.aName
                    };

            //return the IList<Car> that is wanted
            var lst = queryConvertAnonymousToOriginal.ToList();
            return lst;

        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.WriteLine(ex.ToString());
            throw;
        }
    }
}

Quindecennial