CODEWITHSUNDEEP
c#linqlinq-to-sql
jamone
jamone

Reputation: 17419

LINQ query returns way more results than in the entire database

I have the following LINQ query. The problem is it's returning 13k results when tblSurveys only has 20 total. What am I doing wrong?

from s in surveyContext.tblSurveys
from st in surveyContext.tblTypes_for_Surveys
from t in surveyContext.tblSurvey_Types
where (s.Survey_Date >= startDate && s.Survey_Date <= stopDate) && 
      (s.Unsubstantiated || 
         (st.SurveyID == s.SurveyID && st.SurveyTypeID == t.SurveyTypeID && 
         t.UnsubstantiatedAvailable && (from d in surveyContext.tblDeficiencies
                                         where d.SurveyID == s.SurveyID
                                        select d.DeficiencyID).Count() == 0))
orderby s.Survey_Date
select s;

Upvotes: 0

Views: 132

Answers (2)

Ryan Versaw
Ryan Versaw

Reputation: 6495

Do you have foreign keys and relationships setup in your database? If so, you can greatly simplify your query. I'd also recommend renaming your tables in the .dbml file so they aren't all prefixed with 'tbl'.

If you do have relationships setup, your query could look (something) like this:

from s in surveyContext.tblSurveys
where (s.Survey_Date >= startDate && s.Survey_Date <= stopDate) && 
     (s.Unsubstantiated || 
        (s.tblTypes_for_Surveys.Any(st => st.tblSurvey_Type.UnsubstantiatedAvailable) && s.tblDeficiencies.Count() == 0))
orderby s.Survey_Date
select s;

Upvotes: 1

Fredou
Fredou

Reputation: 20140

I can see a cross join in there, look at the <-------

           from s in surveyContext.tblSurveys
           from st in surveyContext.tblTypes_for_Surveys
           from t in surveyContext.tblSurvey_Types
           where (s.Survey_Date >= startDate && s.Survey_Date <= stopDate) && 
                 (s.Unsubstantiated || <-------
                    (st.SurveyID == s.SurveyID && st.SurveyTypeID == t.SurveyTypeID && 
                    t.UnsubstantiatedAvailable && (from d in surveyContext.tblDeficiencies
                                                   where d.SurveyID == s.SurveyID
                                                   select d.DeficiencyID).Count() == 0))
           orderby s.Survey_Date
           select s;

it seem you need to do a left join in here

Upvotes: 5

Related Questions