LINQ-TO-SQL Searching

Posted on 2009-04-22
Last Modified: 2013-11-11
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:



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.

Question by:MikeDotNet555
    LVL 26

    Expert Comment

    by:Anurag Thakur
    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?
    LVL 3

    Author Comment

    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.
    LVL 12

    Expert Comment

    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;
    LVL 62

    Expert Comment

    by:Fernando Soto
    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"

    Lets say the two tables are defined as follows
    CREATE TABLE [dbo].[tblItem](
    	[pkItem] [int] IDENTITY(1,1) NOT NULL,
    	[SomeVal] [int] NULL,
    	[pkItem] ASC
    ) 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,
    	[pkStat] ASC
    ) ON [PRIMARY]
    ALTER TABLE [dbo].[tblStat]  WITH CHECK ADD  CONSTRAINT [FK_tblStat_tblItem] FOREIGN KEY([fkItem])
    REFERENCES [dbo].[tblItem] ([pkItem])
    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
    foreach (var r in results)
        Console.WriteLine(r.pkItem.ToString() + "\t\t" +
                          r.SomeVal.ToString() + "\t\t" +
                          r.pkStat.ToString() + "\t\t" +

    Open in new window

    LVL 62

    Expert Comment

    by:Fernando Soto
    @ wht1986;

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

    LVL 3

    Author Comment

    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:


    -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:

    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:

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

    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.
    LVL 3

    Author Comment

    edit: I think the topic of this post should be more something like: dynamic linq-to-sql query's
    LVL 12

    Expert Comment

    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

    LVL 12

    Expert Comment

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

    Dynamic LINQ
    LVL 3

    Accepted Solution

    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.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
    This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 members asked questions and received personalized solutions in the past 7 days.

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

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now