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 lt Dog gt  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 lt Dog gt  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

User · Answer

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

User · Answer

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 lt Dog gt   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 lt Dog gt  GetDogs         using  var db   new DogDataContext ConnectString                 db LoadOptions LoadWith lt Dog gt  i   gt  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

User · Answer

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   gt  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

User · Answer

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 lt TResult gt  Repeat lt TResult gt  TResult element  int count        for int i 0  i lt count  i                  yield return element            Below an example based on code from original question   var result   GetDogsWithBreedNames  Name  BreedName    gt  new  Name  BreedName       public static IQueryable lt TResult gt  GetDogsWithBreedNames lt TResult gt  Func lt object  object  TResult gt  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

User · Answer

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

User · Answer

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 lt Dog gt  - 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

User · Answer

Well  if you re returning Dogs  you d do   public IQueryable lt Dog gt  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

User · Answer

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

User · Answer

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 lt  string Name  string BreedName  gt  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   gt   r Name  r BreedName   ToList        You might need to install System ValueTuple nuget package though

User · Answer

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 lt Dog gt  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  gt                             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

User · Answer

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

User · Answer

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   gt  new   x Item1  x Item2

User · Answer

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 lt Tuple lt Dog Breed gt  gt  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 lt Dog Breed gt  d  b        return result      And on the calling site   void main         IEnumerable lt Tuple lt Dog Breed gt  gt  dogs   GetDogsWithBreedNames        foreach Tuple lt Dog Breed gt  tdog in dogs                Console WriteLine  Dog  0   1    tdog param1 Name  tdog param2 BreedName

User · Answer

I tend to go for this pattern   public class DogWithBreed       public Dog Dog   get  set        public string BreedName    get  set       public IQueryable lt DogWithBreed gt  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

User · Answer

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 lt Car gt  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 lt Car gt                    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 lt Car gt  that is wanted             var lst   queryConvertAnonymousToOriginal ToList                return lst                     catch  Exception ex                        System Diagnostics Debug WriteLine ex ToString                 throw

User · Answer

Try this to get dynamic data  You can convert code for List lt    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

[c#] Return anonymous type results?

Examples related to c#

Examples related to linq

Examples related to linq-to-sql