Solved

conditional where

Posted on 2013-01-27
3
322 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

685 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