none
Return type Anonymous and searching RRS feed

  • Question

  • I have a DispositionHold class created by the designer when using Linq to SQL. I want to return some fields from this class and also return a count.

     

     var query = (from disposition in db.DispositionHolds
                   group disposition by new
    
                   {
                     disposition.ReportDate,
                     disposition.FileName
                   } into d
                   select new
                   {
                     count = d.Key.FileName.Count(),
                     d.Key.ReportDate,
                     d.Key.FileName
                   });
            return query;

     I want to return a type that I can search in later. If I use IEnumerable I can return the anonymous type but I am having problems searching in the FileName field.

    I tried to return a List<DispositionHold> but there is no count field of course.

    I tried to return an Array type but I can't seem to search in the FileName field either.

    I know I can create a class with the count field but that seems overkill since I may need to return counts for other classes.

    Wednesday, June 23, 2010 4:58 PM

Answers

  • var query = (from disposition in db.DispositionHolds
                   group disposition by new

                   {
                     disposition.ReportDate,
                     disposition.FileName
                   } into d
                   select new Tuple<int, string, string>(d.Count(), d.Key.ReportDate, d.Key.FileName);

    http://msdn.microsoft.com/en-us/library/dd386921.aspx

    Read this
                  

    • Marked as answer by forwheeler Wednesday, June 23, 2010 7:54 PM
    Wednesday, June 23, 2010 7:30 PM

All replies

  • You can't return anonymous types so your options are to use the actual type or to project into another existing type.

    You say you don't want to return the actual type because it doesn't have a count so you could use the Tuple type in .NET 4.0 or roll your own similar generic type that could be re-used a lot.

    [)amien

    Wednesday, June 23, 2010 5:08 PM
    Moderator
  • Could you please elaborate, what type is FileName?

    Regards

    Wednesday, June 23, 2010 5:11 PM
  • Sorry. FileName is a varchar
    Wednesday, June 23, 2010 5:18 PM
  • If it is a varchar it is mapped as a string?  Then Count for a string doesn't exist, use Length instead assuming that what you want is to store the lenght of the FileName, if what you want is the ammount of records selected by your query then you use count on the returned object of your query.

    On accessing the properties on an anonymous type collection, that can only be done by using a foreach...

    Wednesday, June 23, 2010 5:26 PM
  • What I really want is count(*) but I don't know how to do that in Linq. The way I have it now seems to give me the correct count.
    Wednesday, June 23, 2010 5:32 PM
  • Ok I didn't know about tuple and that is what I want I think. Can you help with syntax in Linq?

    I tried

     

     var query = (from disposition in db.DispositionHolds
                   group disposition by new
    
                   {
                     disposition.ReportDate,
                     disposition.FileName
                   } into d
                   select new Tuple<int, string,string>(
                   {
                     count = d.Key.FileName.Count(),
                     d.Key.ReportDate,
                     d.Key.FileName
                   }));
            return (Tuple)query;

     

    Wednesday, June 23, 2010 5:34 PM
  • As I said do you want the ammount of records pulled from the query?

    Wednesday, June 23, 2010 6:05 PM
  • Yes. Here is the original SQL

     

    SELECT COUNT(*)AS Count, ReportDate, FileName
    FROM dbo.DispositionHold
    GROUP BY ReportDate, FileName
    
    Wednesday, June 23, 2010 6:12 PM
  • var query =

    from d in DispositionHold

    group d by new{ d.ReportDate, d.FileName} into g

    select new

    {

    ReportDate = g.Key.ReportDate,

    FileName = g.Key.FileName,

    Count = g.Count()

    };

    Wednesday, June 23, 2010 6:17 PM
  • var query =

    from d in DispositionHold

    group d by new{ d.ReportDate, d.FileName} into g

    select new

    {

    ReportDate = g.Key.ReportDate,

    FileName = g.Key.FileName,

    Count = g.Count()

    };


    Thanks for the help with the count. Now I just need to return a tuple.
    Wednesday, June 23, 2010 6:22 PM
  • IQueryable<DispositionHoldCount> query =
    
    from d in DispositionHold
    
    group d by new{ d.ReportDate, d.FileName} into g
    
    select new DispositionHoldCount
    
    {
    
    ReportDate = g.Key.ReportDate,
    
    FileName = g.Key.FileName,
    
    Count = g.Count()
    
    };
    

    Create a class somethingl ike this:

    class DispositionHoldCount
    {
    public string ReportDate { get; set; }
    public string FileName { get; set; }
    public int Count { get; set; }
    	
    }

    then use that into the query

     

    Wednesday, June 23, 2010 6:32 PM
  • IQueryable<DispositionHoldCount> query =
    
    
    
    from d in DispositionHold
    
    
    
    group d by new{ d.ReportDate, d.FileName} into g
    
    
    
    select new DispositionHoldCount
    
    
    
    {
    
    
    
    ReportDate = g.Key.ReportDate,
    
    
    
    FileName = g.Key.FileName,
    
    
    
    Count = g.Count()
    
    
    
    };
    
    
    
    

    Create a class somethingl ike this:

    class DispositionHoldCount
    
    {
    
    public string ReportDate { get; set; }
    
    public string FileName { get; set; }
    
    public int Count { get; set; }
    
    	
    
    }
    
    

    then use that into the query

     


    Yeah I can create a class but that seems overkill just to return rows of 3 fields.
    Wednesday, June 23, 2010 6:36 PM
  • var query =

    from d in DispositionHold

    group d by new{ d.ReportDate, d.FileName} into g

    select new Tuple.Create(g.Key.ReportDate, g.Key.FileName,g.Count())


    I assume reportdate is datetime, filename is string, etc
    Wednesday, June 23, 2010 6:59 PM
  • Well I go this far but got Create is a method but used as a type.

     

     var query = (from disposition in db.DispositionHolds
                   group disposition by new
    
                   {
                     disposition.ReportDate,
                     disposition.FileName
                   } into d
                   select new Tuple.Create<int, string, string>(d.Count(), d.Key.ReportDate, d.Key.FileName)
                   {
                     Count = d.Key.FileName.Count(),
                     ReportDate = d.Key.ReportDate,
                     FileName = d.Key.FileName
                   });
            return query;
    Wednesday, June 23, 2010 7:20 PM
  • var query = (from disposition in db.DispositionHolds
                   group disposition by new

                   {
                     disposition.ReportDate,
                     disposition.FileName
                   } into d
                   select new Tuple<int, string, string>(d.Count(), d.Key.ReportDate, d.Key.FileName);

    http://msdn.microsoft.com/en-us/library/dd386921.aspx

    Read this
                  

    • Marked as answer by forwheeler Wednesday, June 23, 2010 7:54 PM
    Wednesday, June 23, 2010 7:30 PM
  • var query = (from disposition in db.DispositionHolds
                   group disposition by new

                   {
                     disposition.ReportDate,
                     disposition.FileName
                   } into d
                   select new Tuple<int, string, string>(d.Count(), d.Key.ReportDate, d.Key.FileName);

    http://msdn.microsoft.com/en-us/library/dd386921.aspx

    Read this
                  


    Thanks that works.
    Wednesday, June 23, 2010 7:55 PM