Linq to sql variable where clause

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi, I'm trying to replicate the following query using L2S:

select *
from citation c
inner join citationkeyword kc on c.citationid = kc.citationid
inner join keyword k on kc.keywordid = k.keywordid
where (
k.Keyword like @p0
OR k.Keyword like @p1
OR k.Keyword like @p2
...
OR k.Keyword like @pX
)

And of course there are a variable number of Like clauses. I've tried
this, but I get too many results:

CitationsDataContext
dc;
IQueryable<Citation> result;
IEnumerable<KeywordList.NameValuePair> keyResults;
string keyword;
KeywordList keywordList;

using ( mgr = ContextManager<MedCitationsDataContext>.GetManager
() ) {
keywordList = from kwords in dc.Keyword

keyword = keywords[ 0 ];
keyResults =
from kwords in keywordList
where kwords.Value.Contains( keyword )
select kwords;

for ( int i = 1 ; i < keywords.Length ; i += 1 ) {
keyword = keywords[ i ];
keyResults = keyResults.Union(
from kwords in keywordList
where kwords.Value.Contains( keyword )
select kwords
);
}

var keyIds =
from kr in keyResults
select kr.Key;

result =
from citations in mgr.DataContext.Citation
join citationKeywords in mgr.DataContext.CitationKeyword
on citations.CitationId equals citationKeywords.CitationId
where keyIds.Contains( citationKeywords.KeywordId )
select citations;

Instead, I get all rows in the Citation table returned. Any ideas?
 
select  *
from    citation c
        inner join citationkeyword kc on c.citationid = kc.citationid
        inner join keyword k on kc.keywordid = k.keywordid
where (
        k.Keyword like @p0
        OR k.Keyword like @p1
        OR k.Keyword like @p2
                ...
        OR k.Keyword like @pX
)

And of course there are a variable number of Like clauses.  I've tried
this, but I get too many results:

                                                CitationsDataContext
dc;
                        IQueryable<Citation> result;
                        IEnumerable<KeywordList.NameValuePair> keyResults;
                        string keyword;
                        KeywordList keywordList;

                        using ( mgr = ContextManager<MedCitationsDataContext>.GetManager
() ) {
                                keywordList = from kwords in dc.Keyword

                                keyword = keywords[ 0 ];
                                keyResults =
                                        from kwords in keywordList
                                        where kwords.Value.Contains( keyword )
                                        select kwords;

                                for ( inti = 1 ; i < keywords.Length ; i += 1 ) {
                                        keyword = keywords[ i ];
                                        keyResults = keyResults.Union(
                                                from kwords in keywordList
                                                where kwords.Value.Contains( keyword )
                                                select kwords
                                        );
                                }

                                var keyIds =
                                        from kr in keyResults
                                        select kr.Key;

                                result =
                                        from citations in mgr.DataContext.Citation
                                        join citationKeywords in mgr.DataContext.CitationKeyword
                                                on citations.CitationId equals citationKeywords.CitationId
                                        where keyIds.Contains( citationKeywords.KeywordId )
                                        select citations;

Instead, I get all rows in the Citation table returned.  Any ideas?
Click to expand...

I can't see what would give you that particular result, but the code
above has at least one problem, that of capuring the "keyword"
variable. You write:

keyResults = keyResults.Union(
from kwords in keywordList
where kwords.Value.Contains( keyword )
select kwords

Note that this captures the _variable_ "keyword", not the _value_ of
that variable. Each time you actually execute the query, it will use
the value of "keyword" that is current at the point of execution. In
your case, you keep reassigning "keyword" in the loop, so by the time
you get to the point where you execute your query, its value will be
"keywords[keywords.Length - 1]" - i.e. the last keyword. To avoid
this, you should instead use a variable local to your loop, and
capture that:

for ( int i = 1 ; i < keywords.Length ; i += 1 ) {
var ithKeyword = keywords;
keyResults = keyResults.Union(
from kwords in keywordList
where kwords.Value.Contains(ithKeyword)
select kwords);
}
 
select  *
from    citation c
        inner join citationkeyword kc on c.citationid = kc.citationid
        inner join keyword k on kc.keywordid = k.keywordid
where (
        k.Keyword like @p0
        OR k.Keyword like @p1
        OR k.Keyword like @p2
                ...
        OR k.Keyword like @pX
)

And of course there are a variable number of Like clauses.  I've tried
this, but I get too many results:

                                                CitationsDataContext
dc;
                        IQueryable<Citation> result;
                        IEnumerable<KeywordList.NameValuePair> keyResults;
                        string keyword;
                        KeywordList keywordList;

                        using ( mgr = ContextManager<MedCitationsDataContext>.GetManager
() ) {
                                keywordList = from kwords in dc.Keyword

                                keyword = keywords[ 0 ];
                                keyResults =
                                        from kwords in keywordList
                                        where kwords.Value.Contains( keyword )
                                        select kwords;

                                for ( inti = 1 ; i < keywords.Length ; i += 1 ) {
                                        keyword = keywords[ i ];
                                        keyResults = keyResults.Union(
                                                from kwords in keywordList
                                                where kwords.Value.Contains( keyword )
                                                select kwords
                                        );
                                }

                                var keyIds =
                                        from kr in keyResults
                                        select kr.Key;

                                result =
                                        from citations in mgr.DataContext.Citation
                                        join citationKeywords in mgr.DataContext.CitationKeyword
                                                on citations.CitationId equals citationKeywords.CitationId
                                        where keyIds.Contains( citationKeywords.KeywordId )
                                        select citations;

Instead, I get all rows in the Citation table returned.  Any ideas?
Click to expand...

I can't see what would give you that particular result, but the code
above has at least one problem, that of capuring the "keyword"
variable. You write:

keyResults = keyResults.Union(
from kwords in keywordList
where kwords.Value.Contains( keyword )
select kwords

Note that this captures the _variable_ "keyword", not the _value_ of
that variable. Each time you actually execute the query, it will use
the value of "keyword" that is current at the point of execution. In
your case, you keep reassigning "keyword" in the loop, so by the time
you get to the point where you execute your query, its value will be
"keywords[keywords.Length - 1]" - i.e. the last keyword. To avoid
this, you should instead use a variable local to your loop, and
capture that:

for ( int i = 1 ; i < keywords.Length ; i += 1 ) {
var ithKeyword = keywords;
keyResults = keyResults.Union(
from kwords in keywordList
where kwords.Value.Contains(ithKeyword)
select kwords);
}
 
select  *
from    citation c
        inner join citationkeyword kc on c.citationid = kc.citationid
        inner join keyword k on kc.keywordid = k.keywordid
where (
        k.Keyword like @p0
        OR k.Keyword like @p1
        OR k.Keyword like @p2
                ...
        OR k.Keyword like @pX
)
Click to expand...
And of course there are a variable number of Like clauses.  I've tried
this, but I get too many results:
Click to expand...
                                               CitationsDataContext
dc;
                        IQueryable<Citation> result;
                        IEnumerable<KeywordList..NameValuePair> keyResults;
                        string keyword;
                        KeywordList keywordList;
Click to expand...
                        using ( mgr = ContextManager<MedCitationsDataContext>.GetManager
() ) {
                                keywordList = from kwords in dc.Keyword
Click to expand...
                                keyword= keywords[ 0 ];
                                keyResults =
                                       from kwords in keywordList
                                       where kwords.Value.Contains( keyword )
                                       select kwords;
Click to expand...
                                for ( int i = 1 ; i < keywords.Length ; i += 1 ) {
                                       keyword = keywords[ i ];
                                       keyResults = keyResults.Union(
                                               from kwords in keywordList
                                               where kwords.Value.Contains( keyword )
                                               select kwords
                                       );
                                }
Click to expand...
                                var keyIds =
                                       from kr in keyResults
                                       select kr.Key;
Click to expand...
                                result =
                                       from citations in mgr.DataContext.Citation
                                       join citationKeywords in mgr.DataContext.CitationKeyword
                                               on citations.CitationId equals citationKeywords.CitationId
                                       where keyIds.Contains( citationKeywords.KeywordId )
                                       select citations;
Click to expand...
Instead, I get all rows in the Citation table returned.  Any ideas?
Click to expand...

I can't see what would give you that particular result, but the code
above has at least one problem, that of capuring the "keyword"
variable. You write:

  keyResults = keyResults.Union(
    from kwords in keywordList
    where kwords.Value.Contains( keyword )
    select kwords

Note that this captures the _variable_ "keyword", not the _value_ of
that variable. Each time you actually execute the query, it will use
the value of "keyword" that is current at the point of execution. In
your case, you keep reassigning "keyword" in the loop, so by the time
you get to the point where you execute your query, its value will be
"keywords[keywords.Length - 1]" - i.e. the last keyword. To avoid
this, you should instead use a variable local to your loop, and
capture that:

    for ( int i = 1 ; i < keywords.Length ; i += 1 ) {
      var ithKeyword = keywords;
      keyResults = keyResults.Union(
        from kwords in keywordList
        where kwords.Value.Contains(ithKeyword)
        select kwords);
   }- Hide quoted text -

- Show quoted text -
Click to expand...


Ahh... thanks for pointing that out. That certainly would be a
problem.
 
Back
Top