• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

LINQ-TO-SQL Searching

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.
0
MikeDotNet555
Asked:
MikeDotNet555
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Anurag ThakurCommented:
i dont really understand the question
actually what do you want to shortlist your where clause to
can you explain the conditions for the where?
0
 
MikeDotNet555Author Commented:
I need to implement a search with specific conditions.

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.
0
 
wht1986Commented:
Assuming your Linq to Sql Mapping (dbml) was called DataClassesDataContext

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;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Fernando SotoCommented:
Hi MikeDotNet555;

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());
}

Open in new window

0
 
Fernando SotoCommented:
@ wht1986;

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

Fernando
0
 
MikeDotNet555Author Commented:
thx for the reply wht1986,

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.
0
 
MikeDotNet555Author Commented:
edit: I think the topic of this post should be more something like: dynamic linq-to-sql query's
0
 
wht1986Commented:
I'm heading to bed at moment, but below is how i handle dynamically adding where clauses based on textbox values etc.  Finally i could union the seperate where clauses together.

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 });

Open in new window

0
 
wht1986Commented:
I'm sure you've seen it but I'll post it here in case anyone else comes along who hasnt

Dynamic LINQ http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
0
 
MikeDotNet555Author Commented:
Minimising the join as much as possible seem to help. If LINQ have to do relation mapping in huge tables it is REALLY slow.

The basic idea I implemented is:

-Filter the results as much as possible doing 1-table query's
-Then join those tables and filter the multi-table requirements.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now