LINQ to SQL
I am finishing up a C # ASP.NET program that allows the user to build their own computer by selecting hardware components such as memory, processor, etc. from dropdowns. SQL datatable has 3 columns; ComputerID attribute and value. Computer ID is the ID that corresponds to a specific computer in my main data file, Attribtute is the name of the hardware component; memory, processor, hard drive, etc., and the value is the value assigned to this attribute, such as 1 GB or 2.8 GHz 320 GB. This means that the computer will have multiple attributes.
What I am trying to do is narrowing down the results by first selecting all computers that meet the first requirement of an attribute and then getting from that list all computers that meet the next requirement .. and so on for about 10+ attributes.
I thought it would be nice to show you an example of my LINQ to SQL query so you have an idea of what I am trying to do. It mostly picks ComputerID where computers have more than 1GB memory.
var resultsList = from results in db.ComputerAttributes
where computer.Value == "MEMORY" && computer.Value >= "1"
select results.ComputerID;
Next I want to select from the list of results where the processor says faster than 2.8 GHz, etc.
I hope I gave you enough information. If anyone could give me some advice on how I can finish this project, that would be great.
thank
You need to use Concat as "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 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();
source to share
I assume you are asking:
How can I improve the search content I have done with LINQ to SQL?
Or something like that straight out?
As far as I understand, you have two options:
- Filter the results in memory (if you've cached them).
- Expand your SQL query and hit DB again.
I'm not sure if LINQ to SQL allows you to query an existing result set. I’m sure it’s not.
source to share
You might want to switch to using extension methods. This can make it easier to combine them. Also, you won't be able to do less or more than string comparisons because SQL doesn't support these types of string comparisons. However, if you have kept the IDs in the same order as the string descriptions, you can compare them.
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 example above, 1000 is equivalent to 1 GB and 2800 is equivalent to 2.8 GHz.
source to share
Wow, thanks everyone for your advice, it was extremely helpful. I'll try your methods and tell you how things are going. David B's last solution was very similar to what I did before (counting ComputerID cases), 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;
}
}
}
Let's pick the best answer after trying each one. Thanks again for your help, it was life saving.