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

conditional where

This is not producing a where clause in SQL. What am I doing wrong.

 using (var db = new CERTContext())
                    {
                        var search = db.Project.Include("ValueStream").Include("Function").AsQueryable();
                        if (!lookForDate)
                        {
                            search.Where(p => p.ProjectName.Contains(searchText) || p.ProjectDescription.Contains(searchText) || p.EquipmentName.Contains(searchText) || p.ValueStream.Description.Contains(searchText) || p.ValueStream.Description.Contains(searchText)
                                || p.TrackingId.Contains(searchText) || p.ENumber.Contains(searchText) || p.ShamrockNumber.Contains(searchText));
                            //.Take(10)
                            //.OrderByDescending(p => p.ProjectID);
                            //.ToList();
                        }
                        else
                        {// Search on date
                            search.Where(p => (System.Data.Objects.EntityFunctions.TruncateTime(p.StartDate) == searchDate));
                            //.Take(10)
                            //.OrderByDescending(p => p.ProjectID);
                            //.ToList();
                        }
                                                

                        if (showDeleted != true)
                        {
                            
                           search.Where(p => p.Deleted == false);
                        }

                        pList = search.Take(10).OrderByDescending(p => p.ProjectID).ToList();

                                       
       
                    }// using (var db = new CERTContext())

Open in new window


This is what I am getting.

SELECT
[Limit1].[ProjectID] AS [ProjectID],
[Limit1].[FunctionId] AS [FunctionId],
[Limit1].[ValueStreamId] AS [ValueStreamId],
[Limit1].[TrackingId] AS [TrackingId],
[Limit1].[VersionNo] AS [VersionNo],
[Limit1].[VersionDate] AS [VersionDate],
[Limit1].[ProjectName] AS [ProjectName],
[Limit1].[Deleted] AS [Deleted],
[Limit1].[Closed] AS [Closed],
[Limit1].[ProjectLead] AS [ProjectLead],
[Limit1].[ProjectDescription] AS [ProjectDescription],
[Limit1].[StartDate] AS [StartDate],
[Limit1].[CurrentPhase] AS [CurrentPhase],
[Limit1].[ENumber] AS [ENumber],
[Limit1].[ShamrockNumber] AS [ShamrockNumber],
[Limit1].[EquipmentName] AS [EquipmentName],
[Limit1].[ConceptExpectedEndDate] AS [ConceptExpectedEndDate],
[Limit1].[PlanningExpectedEndDate] AS [PlanningExpectedEndDate],
[Limit1].[DesignExpectedEndDate] AS [DesignExpectedEndDate],
[Limit1].[InstallExpectedEndDate] AS [InstallExpectedEndDate],
[Limit1].[DeployExpectedEndDate] AS [DeployExpectedEndDate],
[Limit1].[ConceptActualEndDate] AS [ConceptActualEndDate],
[Limit1].[PlanningActualEndDate] AS [PlanningActualEndDate],
[Limit1].[DesignActualEndDate] AS [DesignActualEndDate],
[Limit1].[InstallActualEndDate] AS [InstallActualEndDate],
[Limit1].[DeployActualEndDate] AS [DeployActualEndDate],
[Limit1].[ScalingQ1Text] AS [ScalingQ1Text],
[Limit1].[ScalingQ2Text] AS [ScalingQ2Text],
[Limit1].[ScalingQ3Text] AS [ScalingQ3Text],
[Limit1].[ScalingQ1Answer] AS [ScalingQ1Answer],
[Limit1].[ScalingQ2Answer] AS [ScalingQ2Answer],
[Limit1].[ScalingQ3Answer] AS [ScalingQ3Answer],
[Limit1].[ScalingNotes] AS [ScalingNotes],
[Limit1].[CreateUserName] AS [CreateUserName],
[Limit1].[LastUserName] AS [LastUserName],
[Limit1].[CreatedDate] AS [CreatedDate],
[Limit1].[EditedDate] AS [EditedDate],
[Extent2].[ValueStreamId] AS [ValueStreamId1],
[Extent2].[Description] AS [Description],
[Extent3].[FunctionID] AS [FunctionId1],
[Extent3].[Description] AS [Description1]
FROM    (SELECT TOP (10) [c].[ProjectID] AS [ProjectID], [c].[FunctionId] AS [FunctionId], [c].[ValueStreamId] AS [ValueStreamId], [c].[TrackingId] AS [TrackingId], [c].[VersionNo] AS [VersionNo], [c].[VersionDate] AS [VersionDate], [c].[ProjectName] AS [ProjectName], [c].[Deleted] AS [Deleted], [c].[Closed] AS [Closed], [c].[ProjectLead] AS [ProjectLead], [c].[ProjectDescription] AS [ProjectDescription], [c].[StartDate] AS [StartDate], [c].[CurrentPhase] AS [CurrentPhase], [c].[ENumber] AS [ENumber], [c].[ShamrockNumber] AS [ShamrockNumber], [c].[EquipmentName] AS [EquipmentName], [c].[ConceptExpectedEndDate] AS [ConceptExpectedEndDate], [c].[PlanningExpectedEndDate] AS [PlanningExpectedEndDate], [c].[DesignExpectedEndDate] AS [DesignExpectedEndDate], [c].[InstallExpectedEndDate] AS [InstallExpectedEndDate], [c].[DeployExpectedEndDate] AS [DeployExpectedEndDate], [c].[ConceptActualEndDate] AS [ConceptActualEndDate], [c].[PlanningActualEndDate] AS [PlanningActualEndDate], [c].[DesignActualEndDate] AS [DesignActualEndDate], [c].[InstallActualEndDate] AS [InstallActualEndDate], [c].[DeployActualEndDate] AS [DeployActualEndDate], [c].[ScalingQ1Text] AS [ScalingQ1Text], [c].[ScalingQ2Text] AS [ScalingQ2Text], [c].[ScalingQ3Text] AS [ScalingQ3Text], [c].[ScalingQ1Answer] AS [ScalingQ1Answer], [c].[ScalingQ2Answer] AS [ScalingQ2Answer], [c].[ScalingQ3Answer] AS [ScalingQ3Answer], [c].[ScalingNotes] AS [ScalingNotes], [c].[CreateUserName] AS [CreateUserName], [c].[LastUserName] AS [LastUserName], [c].[CreatedDate] AS [CreatedDate], [c].[EditedDate] AS [EditedDate]
      FROM [dbo].[Projects] AS [c] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[ValueStreams] AS [Extent2] ON [Limit1].[ValueStreamId] = [Extent2].[ValueStreamId]
LEFT OUTER JOIN [dbo].[Functions] AS [Extent3] ON [Limit1].[FunctionId] = [Extent3].[FunctionID]
ORDER BY [Limit1].[ProjectID] DESC
0
Eamon
Asked:
Eamon
1 Solution
 
mlmccCommented:
Where in the code are you displaying the SQL or is this in the debugger?

mlmcc
0
 
StephanLead Software EngineerCommented:
The best way to have conditional where statement is using an IQueryable<T> variable. You se this after each statement, and when you want to run the query. Put for example a ToList() at the end to run it.

Here is an example:

var query = context.TableName.Include("SomeForeignTable").Where(x => x.IsVisible == true).AsQueryable();

if (!showDeleted)
{
       query = query.Where(x => x.IsDeleted == false).AsQueryable();
}
var results = query.ToList();

Open in new window

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now