help linqifying

sanderst · December 19th, 2008, 10:48 AM

Ive been trying to create an ecommerce site as part of my final year project for uni. Ive been trying to follow the design shown in the linqifying the beer house ebook.

In my shop I have a PromotedProducts table which contains a list of ProductIDs.

I have been trying to create a static method in my BLL products.cs which will return IEnumerable<ecom_Products>.

have come up with something like

Code:

return context.ecom_PromotedProducts.ecom_Products.OrderByDescending(c => c.Importance).Skip(startRowIndex).Take(maximumRows).ToList();

but am getting an error saying there is no declaration in ecom_PromotedProducts. Can you give me any pointers as to what I need to be doing ?

btw.. am looking forward to the next version of the book based on .net3.5 , just a shame it wasnt out in time for me to base my project on it

Lee Dumond · December 19th, 2008, 12:56 PM

It is not immediately apparent from your post what you have in your db schema as a relationship between the ecom_PromotedProducts table and the ecom_Products table.

I assume one of these tables has an Importance field, and you want to return paged records sorted by Importance in descending order, correct?

sanderst · December 19th, 2008, 01:25 PM

yep. the PromotedProducts table has the importance field and has the productID as a foreign key

Lee Dumond · December 19th, 2008, 02:13 PM

What is the exact error you are getting?

Lee Dumond · December 19th, 2008, 04:15 PM

Okay, never mind, I looked more closely at your syntax and can see your issue.

You seem to ne misunderstanding how to access properties located in a related table. You'd want to do something like this:

Code:

context.ecom_Products.OrderByDescending(p => p.ecom_PromotedProduct.Importance).ToList();

Even given this, I am still scratching my head with what you appear to be trying to do. Is it really List<ecom_Product> or List<ecom_PromotedProduct> you want to return?

Why would you want to return a list of Products ordered by the importance of their related PromotedProducts?

sanderst · December 22nd, 2008, 08:30 AM

see next post

sanderst · December 22nd, 2008, 08:41 AM

thanks again for your help ! is much appreciated. Got one more thing I wanted to put to you to do with the overall design of the system.

To try and explain what i was doing with the PromotedProduct table:

all my products are contained in the products table.

I also have the ability to display certain products on the homepage , and to order them in any order through the cms.

The items i want to promote and their order is determined through a table called PromotedProducts. I also need to be able to add any number of images to the products and to be able to add fields dynamically. I have done this throught two tables called ProductFields and ProductImages.

Here is a link to a screen shot of my database tables.
http://img392.imageshack.us/img392/2342/dbtablesma0.jpg
http://img392.imageshack.us/img392/d...jpg/1/w738.png

I am trying to trying to base my design on part 2 of the linqifying the beer house book.

I have been learning as I go along but one thing that strikes me is that it seems that what I am going to be creating in the BLL using these linq statements are going to be anonymous classes? It seems to make sense that the products data the BLL returns should combine the item fields and the images tables. I think I have found out how to return what I need for the products using something like the following

Code:

from product in context.ecom_products
select new {
product,
fields = 
from productFields in context.ecom_productfields
where productFields.productId == product.productId select productField,
productImages = 
from productImages in context.ecom_productImages
where productImages.productId == product.productId select productImages
}

I havent checked this code yet as is from an example i found, but think the general idea is there.

Is this a good way of doing things? The face that most of my data going to the UI is going to be in the form of anonymous classes worries me abit. Should I declare classes for this data? and would that mean I should go back into the linqtosql class and change the namespace back to DAL (the second part is now BLL as I am trying to follow the part2 of linquifying tbh).

Any guidance you can give me in how I should be structuring my design would be greatly appreciated , as it will soon be too late to change it.

Thanks for your quick replies.

Tom

Lee Dumond · December 24th, 2008, 07:24 PM

It seems to me you may be making this more complicated than it really needs to be.

From what I can gather, it doesn't appear that having a separate ecom_PromotedProducts table is really necessary here. It might be easier to simply have an extra bit field in the ecom_Products table called IsPromoted to indicate whether an particular products is promoted or not.

Then, you could return lists of type ecom_Product (either paged or unpaged) consisting of only "promoted" products by doing the following:

Code:

using System.Collections.Generic;
using System.Linq;
using Ecom.DAL;

namespace Ecom.BLL
{
   public partial class ecom_Product
   {
      public static IEnumerable<ecom_Product> GetPromotedProducts()
      {
         using (ProductsDataContext context = new ProductsDataContext())
         {
            return GetPromotedProducts(0, int.MaxValue);
         }
      }

      public static IEnumerable<ecom_Product> GetPromotedProducts(int startRowIndex, int maximumRows)
      {
         using (ProductsDataContext context = new ProductsDataContext())
         {
            return context.ecom_Products
               .Where(p => p.IsPromoted)
               .OrderByDescending(p => p.Importance)
               .Skip(startRowIndex)
               .Take(maximumRows)
               .ToList();
         }
      }
   } 
}

If you really, really need to have the extra ecom_PromotedProducts table and still return IEnumerable<ecom_Product>, you would need to use LINQ Inheritance. That is a deeper level of "relationship" than merely using a foreign key. That's not covered in the book, but I could explain it further if the solution presented above is not sufficient.

sanderst · December 30th, 2008, 01:42 PM

cheers again for your reply , im going to read further into linq inheritance . i suppose i could get away with using the same table for the promoted items. The reason i hadnt was so that they could be ordered independantly , which i suppose i could do with an extra importance value but think if i do use the linq inheritance things should be abit tidyer.

Im thinking that for the productimages and productfields tables that the best course of action will be to create a new class which will include these fields so that i avoid having such an important class being returned anonymously. would you think that this is the best way to go? Is this a good way of doing things? The face that most of my data going to the UI is going to be in the form of anonymous classes worries me abit. would that mean I should go back into the linqtosql class and change the namespace back to DAL (the second part is now BLL as I am trying to follow the part2 of linquifying tbh).

thanks again for your help

Lee Dumond · December 31st, 2008, 12:51 AM

If you want to query PromotedProducts and return Products, then inheritance is the only way I can think of to do that. The way you have it, a Product and a PromotedProduct are two completely separate entities. You can't query one and return the other. Maybe you should look at your plan again and determine if you really need to return List<Product>, or can you return List<PromotedProduct> instead?

As to the second part of your question, if you read my book, you will notice that I point out that you cannot return an anonymous type from a method. Methods can only return concrete (real) types. That means that if you want to pass an object from the BLL to the UI, you'll have to create an instantiable class to hold it. I provide an example of this in the book (I think it was called articlesNoBody, I can't remember exactly, I wrote the thing three months ago, gimme a break. ;-)

Also, as far as the logical separation is concerned -- there is nothing "magical" about the namespaces. This application is "n-tiered" in the book by placing the DataContext in the DAL and the Entities in the BLL. That's just logically where they belong. You can put them both in the same namespace if you want, but that won't let you pass anonymous types or anything like that.