Search with filters code

I have this method for searching a DB based on filters I was wondering if anyone thought as I do that this code is excessive, and secondly if you have any suggestions to shorten the code.

```
private const string NO_FILTER = "No filter";

[DataObjectMethod(DataObjectMethodType.Select)]
public static List GetPartsSearch(string partNumber, string modelNumber, string slotNumber, string yardNumber, int commodityId, string description)
{
using (var context = new EntitiesModel())
{
IQueryable parts;

if (slotNumber != NO_FILTER)
{
if (modelNumber != "0")
{
if (yardNumber != NO_FILTER)
{
if (commodityId > 0)
{
// Have slot, model, yard and commodity
parts = from part in context.Parts
join partModel in context.PartsByModels on part.PartNumber equals
partModel.PartNumber
join partSlot in context.PartsBySlots on part.PartNumber equals
partSlot.PartNumber
join partYard in context.PartsByYards on part.PartNumber equals
partYard.PartNumber
join commodity in context.Commodities on part.CommodityId equals
commodity.CommodityID
where partSlot.SlotNumber == slotNumber
&& partModel.ModelNumber == modelNumber
&& partYard.YardNumber == yardNumber
&& commodity.CommodityID == commodityId
select part;
}
else
{
//Have slot, model and yard
parts = from part in context.P

It's definitely excessive. :)

I'd start by looking into whether there should be one method for getting the data without a commodity filter, and one method for the ones with commodity.

I'd also put each query into its own method anyway. That will give you quite a bit more readable main function(s).

But to be frank, I wonder why you don't have navigation properties on your entities, and just eagerload them by .Include (if EF) or use a DataLoadOptions set (if L2S).
That way you could ditch all the joins from all the queries.

Also, if you change to the extension syntax, you can add where statements to the queryable.

For instance (EntityFramework eagerloading):

var queryable = context.Parts;
if (shouldIncludeModel)
    queryable = queryable.Include("Model");
if (model > 0)
    queryable = queryable.Where(p => p.Model.Id == model);
if (shouldIncludeYard)
   queryable = queryable.Include("Yard");
if (yard > 0)
    queryable = queryable.Where(p => p.Yard.Id == yard);

return queryable.ToList();

etc.

This should of course also be further refactored in many a better way, at least separated in methods responsible for each of the related entities.
Anyway, I think you'll get quite a bit shorter methods by doing this.

Building on palacsints suggestion, you could also check out how to build Linq expressions manually, but that is quite advanced and cumbersome.
There's some info here: http://msdn.microsoft.com/en-us/library/bb397951.aspx

StackExchange Code Review Q#6913, answer score: 2