Sorting on custom property

Mar 25, 2009 at 2:34 PM
Hi!

Any idea on how to sort on a custom property?

Imagine this dummy database lay-out: company table, department table, employee table with a one to many relationship between company and department and between department and employee. Add a custom property on the Company object, let's say EmployeeCount. How must I implement it to be able to automatically sort a GridView on the EmployeeCount property?

Thanks a lot in advance for your support.
Fabian
Developer
Mar 30, 2009 at 8:34 AM
Remember that linq to sql is translated into a sql statement at the end of the day. It is then executed and only the correct rows are returned, otherwise we would need to load the entire database into memory and then apply filter.

So you probably want to create a view on a table that will implement this extra column. Then you could run a query on the view. For updates to the view, you would need to implement a custom repository helper (controller) that updates the correct linq to sql entites. I have customised the repository helper, so replace SelectAll() with TableName

The other way to pull this off is to select everything into memory and filter it. Create a type (don't use anonymous types) and then do a select returning a the new type.
e.g.
    [DataContract]
    [Serializable]
    public class GalleryImagePrimaryKey
    {
        [DataMember]
        public int Id { get; set; }

        [DataMember]
        public bool IsBlog { get; set; }
    }

   [Serializable]
    public class GalleryImage
    {
        public GalleryImagePrimaryKey Id { get; set; }
        public int BlogShareTypeId { get; set; }
        public int BlogStatusId { get; set; }
        public int? CreatedUserId { get; set; }
        public DateTime? DateCreated { get; set; }
        public string Filename { get; set;}
    }



    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static IQueryable<GalleryImage> SelectAll()
        {
            var galleryImagesForBlog =
                BlogRepositoryHelper.SelectAll().Where(x => x.BlogTypeId == (int) BlogTypeEnum.Image).SelectMany(x=>x.BlogMedias).Select(x=>
                new GalleryImage
                {
                    Id = new GalleryImagePrimaryKey { Id = x.BlogId, IsBlog = true },
                    BlogShareTypeId = x.Blog.BlogShareTypeId,
                    BlogStatusId = x.Blog.BlogStatusId,
                    CreatedUserId = x.Blog.CreatedUserId,
                    DateCreated = x.Blog.DateCreated,
                    Filename = x.Media.Id + x.Media.Filename.Substring(x.Media.Filename.LastIndexOf('.')),
                });

            var galleryImagesForGallery = GalleryRepositoryHelper.SelectAll().SelectMany(x=>x.GalleryMedias).Select(x =>
                new GalleryImage
                {
                    Id = new GalleryImagePrimaryKey { Id = x.GalleryId, IsBlog = false },
                    BlogShareTypeId = x.Gallery.BlogShareTypeId,
                    BlogStatusId = x.Gallery.BlogStatusId,
                    CreatedUserId = x.Gallery.CreatedUserId,
                    DateCreated = x.Gallery.DateCreated,
                    Filename = x.Media.Id + x.Media.Filename.Substring(x.Media.Filename.LastIndexOf('.')),
                });

            return galleryImagesForBlog.Concat(galleryImagesForGallery);
        }

[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static IQueryable<GalleryImage> SelectAll(string sortExpression)
        {
            if (string.IsNullOrEmpty(sortExpression))
            {
                return SelectAll();
            }
            return SelectAll().OrderBy(sortExpression);
        }

Regards
  Taliesin
Mar 30, 2009 at 8:40 AM
Thanks for sharing your experience. Even if your solution looks better than other things I've done, I'm still looking for something more generic.

In your opinion, could we have a custom property that return an Expression that can be used by Linq in order to translate it to SQL?