.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Problems writing a dynamic L2E query

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :ASP.Net
I'm trying to re-work a L2E query to be more dynamic, but I'm not having much luck. Basically, I have two parameters (and many more to come, just laying the foundation), and the parameters are both optional from a user-endpoint.Originally I wrote this static expression:int personnelId = 1234; int divisionId = 1234; var results = (from a in ctx.Attendees from d in a.Divisions join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId && d.DivisionID == divisionId select new Attendee { firstName = a.FirstName, lastName = a.LastName });Attendee is a POCO. After I wrote this, I realized that if personnelId or divisionId weren't passed in (or if just one were passed in) I'd want a different result set. I'm conceptualizing the idea like this (doesn't compile, but you get the drift):var results = (from a in ctx.Attendees select new Attendee { firstName = p.FirstName, lastName = p.LastName }); if (personnelId != null) { results = (from a in results join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId select a); } if (divisionId != null) { results = (from a in results from d in a.Divisions where d.DivisionID == divisionId select a); } results = results.ToList();Doesn't work well though, because my

View Complete Post

More Related Resource Links

Dynamic query for Null field checking

Hi I want dynamiclly find the table to null values finding.. Please give me quickly   Regards Ram

Dynamic query for datalength field checking

Hi   Please tell me the dynamic query length in table

Dynamic query for Null field checking

Hi I want dynamically check the data Formation query in SQL server please  provide me the query

Sub Query Problems

Apologies if I have not posted this in the correct section.  I am having some difficulties with some sub queries. First I'd like to show the database design. Rez_Desc Table Rez_ID Rez_Number Client_Name Arriving_ID Pickup_ID 1001 201000123 Mr. Ross 1 2 1002 201000124 Mrs. Smith 2 1 Arrival_Desc Table Arriving_ID Label 1 AUS 2 USA OffSite_Desc Table OffSite_ID Label 1 AUS 2 USA Ok so this is my table structure, simplified. This is the query I am trying to run. SELECT Rez_Desc.Rez_Number AS RezNum, (SELECT OD.Label FROM Offsite_Desc OD LEFT JOIN Rez_Desc AS RD ON OD.Offsite_ID = RD.Pickup_ID AND RD.Rez_ID=Rez_Desc.Rez_ID) AS Pickup, (SELECT AR.Label FROM Arrival_Desc AR LEFT JOIN Rez_Desc AS RD ON AR.Arriving_ID = RD.Arriving_ID AND RD.Rez_ID=Rez_Desc.Rez_ID) AS DropOff FROM Rez_Desc And I get the following error:- Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. To my knowledge and understanding it is being caused because the sub query is returning more than 1 result which it should not be doing.  Seeing as how in my example table above I have 2 entries, both with relevant ID's etc. Need some help!

Dynamic Class with Linq query Result

Hi, var   query = db.Customers.Where( "City == @0 and Orders.Count >= @1", "London", 10). OrderBy( "CompanyName"). Select( "New(CompanyName as Name, Phone)");    I am using this query as a sample which returns a dynamicclass type, and i need this should to converted to a list collections or datatable etc., As i am holding it in my wcf service with linqtosql as dataaccesslayer. Trying to send the results to the UI which i have consumes this service. Kindly help me in this. Thanks query = db.Customers


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

Dynamic Query alternative?

Hi, I was trying to create a query to get some results for next five years and need to show next five years as Column names. So whatever the year user runs this query, it gives the results for the next five years. Then I realized this was not easy as I expected but managed to implement it using Dynamic Queries.I wrote the following code Ito implement that. But I'm not sure is this the best way to do this.   SET ANSI_NULLS ON  GO  SET QUOTED_IDENTIFIER ON  GO  CREATE PROCEDURE sp_RS_ScenariosforNext5Years  @ticker nvarchar(10),  @variable nvarchar(100)  AS  Declare @SQL varchar(1000)  SELECT @SQL = 'SELECT CASE Scenario '  SELECT @SQL = @SQL + ' WHEN ''A'' THEN ''case1'' '  SELECT @SQL = @SQL + 'WHEN ''B'' THEN ''case2'' '  SELECT @SQL = @SQL + 'WHEN ''C'' THEN ''case3'' '  SELECT @SQL = @SQL + 'WHEN ''D'' THEN ''case4'' '  SELECT @SQL = @SQL + 'WHEN ''E'' THEN ''case5'' '  SELECT @SQL = @SQL + 'WHEN ''F'' THEN ''case6'' '  SELECT @SQL = @SQL + 'WHEN ''G''

Dynamic SQL query cannot store more than 4000 characters even with NVARCHAR(MAX)

Hello, everyone. I am having a rather strange problem when building a dynamic query in SQL Server. I declare a variable of NVARCHAR(MAX) type, and build some queries into it, running them later with sp_executesql. One or two of these queries are so large that they surpass the 4000 char limit that, supposedly, NVARCHAR(MAX) overcomes. However, I still am having the classic problem of storing the whole query inside the variable! I use SQL Server 2008. All the documentation online I checked states that the solution is just using NVARCHAR(MAX) and the problem should not happen. How can this keep happening? Cheers, Ivo Pereira IT Consultant PortugalComputerDoc

Dynamic Linq query?

Please excuse my rookiness but is it possible to make a Linq query dynamic in the following pseduo code (to show what I'm trying to do)Dim objCustomoer = (From c In dc.CustomerLocation _ Where c.customerID = a1 _ And { c.zip = a2  OR  c.city = a3  OR  c.state = a4} _                             Select c).ToList() where a2 and a3  and a4 are being populated from user controls and only one will apply.  So query is done by state,  zip or by city, but not by more than one, so the other two would be null.  So the operator is really "OR" but I haven't found anything on a "or" or "whereOr" operator.  How can I accomplish this?

How to apply dynamic filter in Datasheet View from Query String

The datasheet view doesn't support either connections/dynamic filters from Query string. How to filter the data using query string?

Parse XML To build Dynamic Query

Hi All, I am trying to figure out the best way to handle a scenario where I will be passed XML that will contain criteria for a search. If the user has selected specific filters then those will be sent in the XML and if there is a section that they left unfiltered then it will not be present in the XML (Which would mean everything for that filter should be returned). My question is around the best process to shred the XML and build a dynamic query out of what i am getting out of the XML object. Is there a better way to handle this scenario? Here is my current approach: Shred the XML and put the filtered data into Global Temp Tables so that I can use them to build my dynamic query. Use those temp tables to create "Where Exists" Criteria within the query to filter down the results based on what was passed to me in XML. If one of the search criteria sections wasn't filtered the temp table would have zero rows and I wouldn't add that to the where clause with an exists statement. I used FOR XML PATH('') in the queries to roll up the data into comma separated values. Build the test schema / objects:   -------------------------------------------------------- --Build Test Schema to demonstrate XML Parsing -------------------------------------------------------- SET NOCOUNT ON; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Products]

Problems escaping apostrophe when using an XPath query with SelectSingleNode

I have an XML node which I am trying to search for a specific node which has a given value, but I am having problems when the value contains an apostrophe. I have tried replacing the apostrophe with &apos as follows:                string encodedTitle = title.Replace("'", "&apos;");                 string XmlPath = String.Format("item[title = \'{0}\']", encodedTitle);                 return NodeChannel.SelectSingleNode(XmlPath);                string encodedSearchString = searchString.Replace("'", "&apos;");                string XmlPath = String.Format("item[title = \'{0}\']", encodedSearchString);                return myNode.SelectSingleNode(XmlPath);However, this does not seem to find search strings with an apostrophe (it doesn't throw an exception or anything, it just returns null). Search strings without an apostrophe work fine. Is there any way I can fix this?

Dynamic Textbox List Control - problems refreshing


Ive been stuck on this one for a month:

So I have a custom control that has a list of textboxes, and a button that will dynamically add a new textbox to the list.

 So I have an addTextbox() function, that will do something like

TextbBox t = new TextBox()

 then I add it to the page.  So adding a new textbox works fine, but I have values that I want to put into each textbox and upon each page load, the values get replaced.  What I need to do is get the textboxes from the viewstate so I can use their values for other textboxes, but since they are created anonymously, im not sure how I can do that.  I tried to get the unique name and put all of them into an arraylist, then upon the next page load, load the names from the list, but the unique name isnt unique.  Thanks

dynamic table name linq-to-sql query


I am writing a function in which i generate autocode in specific format i want to make this function generalize so that i send tablename and columnname  parameter and function return the next code. I am using linq to sql with MVC e.g. i want to make table name and column name dyanamic

from m in db.<tblnameparam> 
                       orderby m.<colnameparam> descending
                       select m.<colnameparam>

Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. problems in pivot query




                 I am using following pivot query  but not getting result as single row getting as 4 rows.

    One more problem i am unable to pass dates as parameters to storeprocedure something like this

    select [@date] .

            SELECT 'Forecasted' AS HeadCount,
    [8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010]
    (SELECT *
    FROM TblEmpCount) AS SourceTable
    FOR StartDate IN ([8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010])
    ) AS PivotTable;

    I am getting result as 4 rows but i want result in single row like this

    HeadCount    8/1/2010     8/8/2010    8/15/2010    8/222/2010

    Forecasted     191                182                 176                169





database query problems


Hello All, I have a result table in MS SQL Database server (2005).

Subject   Exam                 SubExam   Year     Marks

Math       First Terminal    CT              2010    18
Math       First Terminal    Hall Exam   2010    67.2

Science   First Terminal    CT              2010    20
Science   First Terminal    Hall Exam   2010    78

Now how can I get the result followningly

                                First Terminal

                        CT       Hall Exam      Total


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend