Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

conditional where

Posted on 2013-01-27
3
Medium Priority
?
328 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 101

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 2000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

885 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