LINQ to SQL

Question

I am finishing off a C# ASP.NET program that allows the user to build their own computer by selecting hardware components such as memory, cpu, etc from a drop down lists. The SQL datatable has 3 columns; ComputerID, Attribute and Value. The computerID is an ID that corresponds to a certain computer in my main datatable of products, the Attribtute is the name of the hardware component; memory,cpu, hard drive etc.. and the value is the value assigned to that attribute, such as 1GB or 2.8GHz 320GB. This means that a computer will have multiple attributes.

What I am trying to do it narrow down the results by first selecting all computers that meet the first attribute requirements and then getting from that list, all computers that meet the next requirement.. and so on for about 10+ attributes.

I thought it might be a good idea to show you an example of my LINQ to SQL query so that you have a btter idea of what I am trying to do. This basically selects the ComputerID where the the computers memory is larger than 1GB.

var resultsList = from results in db.ComputerAttributes
                  where computer.Value == "MEMORY" && computer.Value >= "1"
                  select results.ComputerID;

Next I want to select from the resultsList where the CPU is say, faster than 2.8Ghz and so on.

I hope I have given you enough information. If anyone could please give me some advice as to how I might go about finishing this project that would be great.

Thanks

Answer 1

I'm assuming you're asking:

How do I refine the content of a search I've done using LINQ to SQL?

Or something similar right?

To my understanding, you have two options:

1. Filter your results in memory (if you have them cached).
2. Extend your SQL query and hit the DB again.

I'm not sure that LINQ to SQL allows you to requery an existing result set. I'm pretty sure it doesn't.

Answer 2

You might want to switch to use extension methods. It may make it a little easier to chain these together. Also, you won't be able to do less-than/greater-than comparisons on strings since SQL doesn't support these types of string comparisons. If you stored ids that had the same ordering as the string descriptions, however, you could compare these.

Using extension methods you can do things like:

Dictionary<string,int>  attributeMap = new Dictionary<string,int>();
attributeMap.Add("MEMORY",1000);
attributeMap.Add("SPEED",2800);

var results = db.ComputerAttributes;
foreach (var attribute in attributeMap)
{
    results = results.Where( c => c.Attribute == attribute.Key
                             && c.Value >= attribute.Value );
}

var ids = results.Select( c => c.ComputerID );

foreach (int id in ids)
{
    ...
}

In the above example 1000 is equivalent to 1GB and 2800 is equivalent to 2.8GHz.

Answer 3

You need to use Concat as a "Union All".

IQueryable<ComputerAttribute> results = null;
foreach(ComputerRequirement z in requirements)
{
  //must assign to a locally scoped variable to avoid using
  //  the same reference in all of the where methods.
  ComputerRequirement cr = z;
  if (results == null)
  {
    results = db.ComputerAttributes
      .Where(c => c.Attribute == cr.Attribute && c.Value >= cr.Value);
  }
  else
  {
    results = results
      .Concat(db.ComputerAttributes
         .Where(c => c.Attribute == cr.Attribute && c.Value >= cr.Value)
      );
  }
}

int requirementCount = requirements.Count();

//Get the id's of computers that matched all requirements.
IQueryable<int> ids = results
  .GroupBy(x => x.ComputerId)
  .Where(g => g.Count == requirementsCount)
  .Select(g => g.Key);


//Get all attributes for those id's
List<ComputerAttributes> data = db
  .ComputerAttributes.Where(c => ids.Contains(c.ComputerId))
  .ToList();

Answer 4

Wow, thank you everyone for your advice it has been extremely useful. I'l try your methods and tell you how things go. The last solution by David B was very similar to what I had done before(counting occurrences of ComputerID) however my code was extremely inefficient. :)

        string[] FinalResults = new string[100];
        int Counter2 = 0;
        foreach (string Result in Results)
        {
            string test = Result;
            var Counter1 = 0;
            foreach (string Result2 in Results)
            {
                if (test == Result2)
                {
                    Counter1++;
                }
                else
                {
                    continue;
                }
            }
            if (Counter1 >= 3 && Result != null)
            {
                FinalResults.SetValue(Result,Convert.ToInt32(Counter2));
            }
            else
            {
                continue;
            }
            Counter2++;
        }
        foreach (string FinalResult in FinalResults)
        {
            if (FinalResult != null)
            {
                Response.Write("Laptop: " + FinalResult + "<br />");
            }
            else
            {
                continue;
            }
        }
    }

Will choose the best answer after I have tried each one out. Thanks again everyone for your help, it has been a life saver .