Solved

conditional where

Posted on 2013-01-27
3
320 Views
Last Modified: 2013-01-28
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
Comment
Question by:Eamon
3 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 38825297
Where in the code are you displaying the SQL or is this in the debugger?

mlmcc
0
 
LVL 16

Accepted Solution

by:
Stephan earned 500 total points
ID: 38825692
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

803 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