none
Architecture - building up queries? Can I do the following somehow? RRS feed

  • Question

  • Hi

    I am new to linq to sql & a little rusty with c# so apologies in advance.  I have been constructing a few linq queries and wanted to reuse my code a little more and avoid repetition.  See code below. 

    Instead of creating the whole of GetActiveCustomers twice for each method, I want to use GetActiveCustomers() to form the basis for the overloaded method GetActiveCustomers(int pageIndex, int pageSize).  However because the type of IQuerable is a custom type (not simply customer) I cannot specify it (or don't know how to) and get a (IQueriable does not contain a definition for 'Skip' etc in the second method).

    Is there a way to get around this?  Can I specify a custom type for the IQueryable generic type?  Can you give me any recommendations on architecture to keep my code small and concise.

        public static IQueryable GetActiveCustomers()
     {
            MyDbDataContext db = new MyDbDataContext();

            var query = from c in db.customers
                        where c.status_id == 1
                        select new
                        {
                            newField = ("My custom field),
                            newField2 = ("Another customfield),
       c.name
                        };

            return query;
        }

        public static IQueryable GetActiveCustomers(int pageIndex, int pageSize)
        {
            return GetActiveCustomers().Skip(pageIndex).Take(pageSize);
        }

    Tuesday, June 23, 2009 2:54 PM

Answers


  • Oops.  Since you're data context is getting disposed in GetActiveCustomers(), you can't walk through the IQueryable anymore.  I think for your needs, you'll need to create the data context in the GetActiveCustomers(int,int) method and pass it along into GetActiveCustomers().  Then in GetActiveCustomers() you can use ToList(). Try something along these lines:

            public static IEnumerable<CustomerData> GetActiveCustomers(MyDbDataContext db)
            {
                var query = from c in db.customers
                            where c.status_id == 1
                            select new CustomerData
                            {
                                newField = ("My custom field),
                                newField2 = ("Another customfield),
                                name = c.name
                            };
    
                return query;
            }
    
            public static IEnumerable<CustomerData> GetActiveCustomers(int pageIndex, int pageSize)
            {
                using (MyDbDataContext db = new MyDbDataContext())
                {
                    return GetActiveCustomers(db).Skip(pageIndex).Take(pageSize).ToList();
                }
            }
    
    • Marked as answer by twistedinferno Wednesday, June 24, 2009 11:28 PM
    Tuesday, June 23, 2009 4:38 PM
  • The idea to use a custom type like CustomerData is excellent.

    The problem with having GetActivateCustomer return IEnumerable<T>, though, is that the subsequent pagination will happen on the client rather than the server. This will mean you'll up fetching all customers whose status is 1 to the client, then performing the pagination on the client which is inefficient.

    A better solution is to change GetActiveCustomers so that it returns IQueryable<CustomerData>, as you had before:

      public static IQueryable<CustomerData> GetActiveCustomers()
      {
          return
             from c in new MyDbDataContext().customers
             where c.status_id == 1
             select new CustomerData
             {
                newField = ("My custom field),
                newField2 = ("Another customfield),
                name = c.name
             };
      }

    and then write a Paginate method as follows:

      public static IQueryable<T> Paginate<T> (this IQueryable<T> query, int skip, int take)
      {
         return query.Skip(skip).Take(take);
      }

    Then call it as follows:

       IQueryable<CustomerData> test = GetActiveCustomers().Paginate (50, 10);

    This query will execute entirely on the server, when enumerated. It can also be further composed - for instance, you could then call First() on it and it would be executed as an even more efficient SQL query.

    One other point: you don't need to dispose DataContexts : LINQ to SQL releases connections automatically when the queries are complete.


    Joe

    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by twistedinferno Wednesday, June 24, 2009 11:28 PM
    Wednesday, June 24, 2009 1:02 AM
    Answerer

All replies


  • A custom type is exactly what you want.


            public class CustomerData
            {
                public String newField { get; set; }
                public String newField2 { get; set; }
                public String name { get; set; }
            }
    


    Then your code becomes:


            public static IQueryable<CustomerData> GetActiveCustomers()
            {
                MyDbDataContext db = new MyDbDataContext();
    
                var query = from c in db.customers
                            where c.status_id == 1
                            select new CustomerData
                            {
                                newField = ("My custom field),
                                newField2 = ("Another customfield),
                                name = c.name
                            };
    
                return query;
            }
    
            public static IQueryable<CustomerData> GetActiveCustomers(int pageIndex, int pageSize)
            {
                return GetActiveCustomers().Skip(pageIndex).Take(pageSize);
            }
    
    Tuesday, June 23, 2009 3:31 PM
  • thx v.much fermat.  That looks very promising...

    however, i got the following error message

    The query contains references to items defined on a different data context

    Any ideas?
    Tuesday, June 23, 2009 4:28 PM

  • Oops.  Since you're data context is getting disposed in GetActiveCustomers(), you can't walk through the IQueryable anymore.  I think for your needs, you'll need to create the data context in the GetActiveCustomers(int,int) method and pass it along into GetActiveCustomers().  Then in GetActiveCustomers() you can use ToList(). Try something along these lines:

            public static IEnumerable<CustomerData> GetActiveCustomers(MyDbDataContext db)
            {
                var query = from c in db.customers
                            where c.status_id == 1
                            select new CustomerData
                            {
                                newField = ("My custom field),
                                newField2 = ("Another customfield),
                                name = c.name
                            };
    
                return query;
            }
    
            public static IEnumerable<CustomerData> GetActiveCustomers(int pageIndex, int pageSize)
            {
                using (MyDbDataContext db = new MyDbDataContext())
                {
                    return GetActiveCustomers(db).Skip(pageIndex).Take(pageSize).ToList();
                }
            }
    
    • Marked as answer by twistedinferno Wednesday, June 24, 2009 11:28 PM
    Tuesday, June 23, 2009 4:38 PM
  • The idea to use a custom type like CustomerData is excellent.

    The problem with having GetActivateCustomer return IEnumerable<T>, though, is that the subsequent pagination will happen on the client rather than the server. This will mean you'll up fetching all customers whose status is 1 to the client, then performing the pagination on the client which is inefficient.

    A better solution is to change GetActiveCustomers so that it returns IQueryable<CustomerData>, as you had before:

      public static IQueryable<CustomerData> GetActiveCustomers()
      {
          return
             from c in new MyDbDataContext().customers
             where c.status_id == 1
             select new CustomerData
             {
                newField = ("My custom field),
                newField2 = ("Another customfield),
                name = c.name
             };
      }

    and then write a Paginate method as follows:

      public static IQueryable<T> Paginate<T> (this IQueryable<T> query, int skip, int take)
      {
         return query.Skip(skip).Take(take);
      }

    Then call it as follows:

       IQueryable<CustomerData> test = GetActiveCustomers().Paginate (50, 10);

    This query will execute entirely on the server, when enumerated. It can also be further composed - for instance, you could then call First() on it and it would be executed as an even more efficient SQL query.

    One other point: you don't need to dispose DataContexts : LINQ to SQL releases connections automatically when the queries are complete.


    Joe

    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by twistedinferno Wednesday, June 24, 2009 11:28 PM
    Wednesday, June 24, 2009 1:02 AM
    Answerer
  • Thanks Joe and Fermat.  Really helpful.

    I have managed to get this to work and it provides a part of a solution.  I am new to extension method creation but that looks like it does the job nicely.

    The error I was received:

           The query contains references to items defined on a different data context

     was because I was referencing the where clause with one method and then select in another.  Here is my code.

        public static IQueryable<Customer> GetCustomers(string countryCode)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            return db.Customers.Where(c => c.Country == countryCode);
        }


        public static IQueryable<CustomCustomer> GetCustomersCustom(string countryCode)
        {
            NorthwindDataContext db = new NorthwindDataContext();

            return
             GetCustomers(countryCode).Select(c => new CustomCustomer
             {
                CustomerId = c.Custoemer_id,
                FullContactName = c.ContactName,
                ShortContactName = c.ContactName.Substring(0,3),
                CustomerOrderCount = db.Orders.Where(o => o.CustomerID == c.CustomerID).Count()
             });
        }

    In the GetCustomersCustom method I access the orders table as specified in the bold text.  If I remove this, it runs, and if I specify the whole query in one method it also runs.  Is there a way round what I am trying to do. 

    My goal is to build up the queries so that I don't have to keep repeating the where statements or select statements in each method.  I envisage this saving me time and improving code reuse.  I also want to keep what is being fetched from the database to a minimum.

    Wednesday, June 24, 2009 1:58 PM
  • There are two ways around this.

    The first is to pass in the DataContext as a parameter to each of your static methods (as fermat previously suggested).

    The second is to use Association propeties instead. These will make life much easier - so you should be using them anyway. And as long as you have foreign key constraints set up on your database, they'll be created automatically by Visual Studio's designer or SqlMetal:

        public static IQueryable<CustomCustomer> GetCustomersCustom(string countryCode)
        {
             GetCustomers(countryCode).Select(c => new CustomCustomer
             {
                CustomerId = c.Custoemer_id,
                FullContactName = c.ContactName,
                ShortContactName = c.ContactName.Substring(0,3),
                CustomerOrderCount = c.Orders. Count()
             });
        }

    Notice that we were able to remove the Where clause.

    Joe
    Write LINQ queries interactively - www.linqpad.net
    Thursday, June 25, 2009 12:47 AM
    Answerer
  • Thanks for your help.  I had worked out passing the datacontext sorted this out (I should have actually read the error message and used my brain for a couple of seconds before posting) but using the Association properties make this far neater.  My Northwind database didn't have the customer to order relationship set so that is why it didn't populate the associations automatically.

    I improved my CustomCustomer class a little and inherited from the Customer class from the NorthwindDataContext because I was repeating many of the properties.  So my CustomCustomer class looks little like this...

    public class CustomCustomer : Customer
    {
        public int CustomerOrderCount { get; set; }
    }

    Thursday, June 25, 2009 11:47 AM
  • hi, who can help me?

    public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords){
    // Khai báo danh sách các User.
    MembershipUserCollection list = new MembershipUserCollection();
    // Tạo kết nối
    DataClassesDataContext db = new DataClassesDataContext();
    totalRecords = db.USERs.Count();
    foreach (USER us in db.USERs.Where(p => p.Email == emailToMatch).Skip(pageIndex).Take(pageSize))
    list.Add(
    new MembershipUser(base.Name, us.UserName, null, us.Email, us.Question, string.Empty, true, false, DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now));
    return list;
    }
    i don't understand: "Skip(pageIndex).Take(pageSize)"
    who can explain for me? thank very much!

     

    Wednesday, September 15, 2010 5:09 PM
  • What you don't understand? Skip skips a certain ammount of elements and Take takes a certain ammount of elements (pretty much self explanatory)

    Also if you have a question you should use a new thread.

    Regards

    Wednesday, September 15, 2010 6:14 PM