actually what do you want to shortlist your where clause to

can you explain the conditions for the where?

Solved

Posted on 2009-04-22

Hi, I have a little problem and I would like to know which approach you would take to solve it.

Let's say you have two sql tables in a 1:N relationship, tables could be as follow:

[tblItem]

pkItem

SomeVal

[tblStat]

pkStat

fkItem

Someval1

Someval2

relation would be 1:n tblItem:tblStat.

If you would have to search items that contains specific stats, how would you go about it?

For example:

I want an Item that have the following stats:

stat1: Someval1=4

stat2: Someval2>5

the item could contain 50 stats, but as long as those 2 stats are present then it's ok.

My current search is pretty crappy, while it is perfect fow a low database, when there are thousant of items with millions of stats, the search can take forever.

I am not looking on ways to tweak my current search algorithm, it is so crappy I just want to completly replace it. I am looking for approach suggestions.

Thans.

Let's say you have two sql tables in a 1:N relationship, tables could be as follow:

[tblItem]

pkItem

SomeVal

[tblStat]

pkStat

fkItem

Someval1

Someval2

relation would be 1:n tblItem:tblStat.

If you would have to search items that contains specific stats, how would you go about it?

For example:

I want an Item that have the following stats:

stat1: Someval1=4

stat2: Someval2>5

the item could contain 50 stats, but as long as those 2 stats are present then it's ok.

My current search is pretty crappy, while it is perfect fow a low database, when there are thousant of items with millions of stats, the search can take forever.

I am not looking on ways to tweak my current search algorithm, it is so crappy I just want to completly replace it. I am looking for approach suggestions.

Thans.

10 Comments

actually what do you want to shortlist your where clause to

can you explain the conditions for the where?

I don't know how I could explain this in another way.. I have items with stats and I need to find items with specific stats comparison (> < >= etc.). That might not be possible with a single query, so I am open to any approach that does not rely only on linq.

The problem right now is that to compare the stats I have to check every items and compare every stats, so with a few millions of stats that is just too much. I think the first post explains it pretty well, I am not sure what extra info you would need, I would be glad to provide more info but I really don't know what to say.

Here is how you get all tblitem rows who have a tblStat of someval1 == 4

DataClassesDataContext data = new DataClassesDataContext();

var results = from i in data.tblItems

join s in data.tblStats on i.pkItem equals s.fkItem

where s.Someval1 == 4

select i;

The following code snippet first show how the SQL server tables are set up for the Linq to SQL code. Then as per the question you want to find all Someval1=4 and Someval2>5 for all records in tblStat. The selected values from both table are, from table tblItem the fields pkItem and SomeVal and table tblStat fields pkStat and Someval3 as you did not state what you wanted returned.

This should give you and idea of how it is done in Linq to "MS SQL"

Fernando

```
Lets say the two tables are defined as follows
CREATE TABLE [dbo].[tblItem](
[pkItem] [int] IDENTITY(1,1) NOT NULL,
[SomeVal] [int] NULL,
CONSTRAINT [PK_tblItem] PRIMARY KEY CLUSTERED
(
[pkItem] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblStat](
[pkStat] [int] IDENTITY(1,1) NOT NULL,
[fkItem] [int] NOT NULL,
[Someval1] [int] NULL,
[Someval2] [int] NULL,
[Someval3] [int] NULL,
CONSTRAINT [PK_tblStat] PRIMARY KEY CLUSTERED
(
[pkStat] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblStat] WITH CHECK ADD CONSTRAINT [FK_tblStat_tblItem] FOREIGN KEY([fkItem])
REFERENCES [dbo].[tblItem] ([pkItem])
GO
ALTER TABLE [dbo].[tblStat] CHECK CONSTRAINT [FK_tblStat_tblItem]
==================================================================
Code snippet:
DataClasses1DataContext db = new DataClasses1DataContext();
int firstStat = 4;
int secondStat = 5;
var results = from stat in db.tblStat
where stat.Someval1 == firstStat && stat.Someval2 > secondStat
select new
{
stat.tblItem.pkItem,
stat.tblItem.SomeVal,
stat.pkStat,
stat.Someval3
};
Console.WriteLine("pkItem\tSomeVal\tpkStat\tSomeval3");
foreach (var r in results)
{
Console.WriteLine(r.pkItem.ToString() + "\t\t" +
r.SomeVal.ToString() + "\t\t" +
r.pkStat.ToString() + "\t\t" +
r.Someval3.ToString());
}
```

You do not need the join because the two tables have a relationship that can be used.

Fernando

I am indeed using DataClasesDataContext. Your post make me think I might be a little more specific.

I know alot about linq (at least I think I do) and doing simple query's is really no problem with me, the problem is when I need to do really complex matching stuff.

I will give a more concrete example:

I have a table of items, each in relation 1:n with a table of stats like mentioned above.

Forgot about the tblStat above, check the following one instead:

[tblStats]

pkStat

fkItem

StatType

StatValue

-StatType is an enum listing every stat types

-StatValue is the value of that specific stat

Each stat recorded for an item is a pair of type/value, the type define wich kind of stat is is.

Ok so that is for the data part. Imagine I have 100 000 items with each 10 stats. That means 100 000 items in the items table and 1 000 000 stats in the stats tables.

Now here come the problematic part:

I have a search function in my GUI, the search can define one or many stats with a comparer (> < >= <= == not) and I have to find an effective way to search for items with matching stats.

A more concrete example:

A user want to see all items that have a stat type of 1 with a value equals to 5, he also want to have a stat type of 2 with a value greater than 6, finally he want his item not to contain the stat of type 3. So we could lay this as follow:

-Search criteria 1: Item must contain StatType=1 an associated value = 5

-Search criteria 2: the same item must also contain StatType=2 with an associated value > 6

-Search criteria 3: the same item must not contain StatType=3

so the following item would be valid:

[Item1]

StatType=1 StatValue=5

StatType=2 StatValue=99

StatType=5 StatValue=56 (stats not listed in search criteria are ignored)

while the following items would not be valid:

[Item2]

StatType=2 StatValue=99

StatType=5 StatValue=56 (stat not listed is ignored, but there is a missing stat of type 1)

[Item3]

StatType=1 StatValue=5

StatType=2 StatValue=99

StatType=3 StatValue=1 (excluded because the "not" operator was used

The user can enter an inifite number of search criterias, each having a different or same StatType, comparer and StatValue.

I hope that make a little more sense, this is very complicated to explain (as well as very complicated to achieve) and english is my 2nd language so I sometime have a hard time to explain what I am thinking.

Thanks alot.

But in the really complicated examples I actually like to use the dynamic LINQ extension. It's nice sometimes just to be able to write a where clause like

"(column1 > 5 and column2 < 3) or (column7 = 23)"

without worrying about all the funky linq stuff

```
DataClassesDataContext data1 = new DataClassesDataContext();
var results1 = from i in data1.TestTables
join j in data1.TestTables on i.Column1 equals j.Column2
select new {i, j};
if (this.TextBox1.Text != "")
results1 = results1.Where(x => x.j.Column1 == this.TextBox1.Text);
var results2 = from i in data1.TestTables
join j in data1.TestTables on i.Column1 equals j.Column2
select new { i, j };
if (this.TextBox2.Text != "")
results2 = results2.Where(x => x.j.Column1 >= int.Parse(this.TextBox2.Text));
if (this.TextBox3.Text != "")
results2 = results2.Where(x => x.j.Column1 <= int.Parse(this.TextBox3.Text));
var results = results1.Union(results2).Select(x => new { x.i });
```

Dynamic LINQ http://weblogs.asp.net/sco

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!