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


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

How to convert SQL concatenate to LINQ concatenate query?

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :ASP.Net
 

Hello,

Can you please help me convert the code below to LINQ, converting my project to a wseb version usimg LINQ To SQL. The project contains 10 textboxes to possibly select from, but I'm only providing the first two textboxes to simplify my question.

Thank you.

Code:

mySQL_Statement = "SELECT IDENTIFICATION_DATA.NSC,ITEMISCD.RNC,ITEMISCD.NSC1 FROM IDENTIFICATION_DATA LEFT OUTER JOIN ITEMISCD on IDENTIFICATION_DATA.NIIN = ITEMISCD.NIIN"

 

If Not ((TextBox1.Text = String.Empty) And (TextBox2.Text = String.Empty) Then

mySQL_Statement = mySQL_Statement + " where "

If Not (TextBox1.Text = String.Empty) Then

mySQL_Statement = mySQL_Statement + "IDENTIFICATION_DATA.NSC IN (" + TextBox1.Text & ")"

End If

If (TextBox1.Text = String.Empty) Then

mySQL_Statement = mySQL_Statement + "isnull(IDENTIFICATION_DATA.NIIN) = FALSE"

End If

If Not (TextBox2.Text = String.Empty) Then

If astrixState = 0 Then

If Not (TextBox1.Text = String.Empty) Then

mySQL_Statement = mySQL_Statement + " AND IDENTIFICATION_DATA.NIIN IN (" + TextBox2.Text & ")"

ElseIf (TextBox1.Text = String.Empty) Then

mySQL_Statement = mySQL_Statement + " AND IDENTIFICATION_DATA.NIIN IN (" + TextBox2.Tex


View Complete Post


More Related Resource Links

Convert the Sql query to LINQ .. can anyone please help me I need it ASAP as no time to learn and im

  
 SELECT DISTINCT HP.PartNumber, HP.MIRevision, HPEXData.InstructionImage, EXAttr.PlacementImage from dbo.HardwareParts HP   INNER JOIN dbo.HardwarePartEXData HPEXData     ON HP.HardwarePartID = HPEXData.HardwarePartID   LEFT JOIN dbo.EXMIAttributes EXAttr    ON EXAttr.EXTypeID = HPEXData.EXTypeID   WHERE    HP.PartNumber = @PartNumber     AND REPLACE(SUBSTRING(HP.MIRevision, 1, 13),'.','') >= REPLACE(SUBSTRING(ISNULL(@PartRevision,''), 1, 13),'.','')    AND ISNULL(HP.ProjectID,'') = ISNULL(@SINumber,'')    AND (@ChassisIdentifier IS NULL OR HPEXData.EXMI_Type NOT IN ('Add In Card','Storage') OR EXAttr.ChassisIdentifier = @ChassisIdentifier)   END;anji

Query returns correct rows but I am not able to concatenate the rows into a variable :(

  

Hi all,

Why in the code below I am able to list all rows and display the field 'stepID' but I am not able to concatenate them all in the variable @stepIds?

declare @date as datetime
set @date = '2010-09-20 23:01:20:000'
declare @instance as uniqueidentifier
set @instance = '04658F34-CBF1-4CCD-A683-F23BDB669D35'

  Declare @CrLf char(2)
  Set @CrLf = Char(13) + Char(10)
	Declare @stepIds varchar(8000)
	Set @stepIds = ''
	
	Select
		--@stepIds = @stepIds + ' - Step: ' + CONVERT(varchar(4), stepID)
		stepID
	From
		Uds.RealtimeLogging
	

Convert sql query in linq

  

hello guys,


how convert this sql query in linq,


SELECT COUNT(AdvisorID) AS AdvisorRegisterLast2Days FROM ob_Advisor WHERE CONVERT(varchar,CreationDate,101) BETWEEN CONVERT(varchar,DATEADD(day,-4,DATEADD(day,2,GETDATE())),101) AND CONVERT(varchar,GETDATE(),101)


Plese help me out

thanks


Query to concatenate

  

I am extracting the content from PDF files using PDFBox tools and I get all the information in one single column. I have something similar to the following:

Column 1

abc

123-456-7890

University of abcxyz, GA

E-mail : abc@abcxyz.com

def

444-444-4444

University of DEFGHI

E-mail : def@defghi.com

How do you concatenate using LINQ?

  

Hello,

I would like to select multiple rows in a grid and concatenate my query with the "or" statement, however the code below doesn't work, any ideas what's is wrong with it?

Code:

Dim vc1 As Integer

For Each row As GridViewRow In GridView2.Rows

'Find checkbox in the row

Dim chb As CheckBox = DirectCast(row.FindControl("CheckBox1"), CheckBox)

'if checkbox is found and is checked then add the id to string

If chb IsNot Nothing AndAlso chb.Checked Then

If vc1 > 0 Then

yx = yx Or ListBox1.SelectedValue.ToString ****Error Line

Else

yx = "" & row.Cells(1).Text & ""

End If

vc1 = vc1 + 1

End If

Next

Thanks,

Victor


How to concatenate texts in LINQ to SQL?

  

I want to concatenate before displaying it on the DatList control. Here's what I have:

findWordDataContext fndWord = new findWordDataContext();

        var word = from w in fndWord.consonants select w + "'" + txtFind.Text + "'";

        dtlFind.DataSource = word;
        dtlFind.DataBind();


Here's the error I got:

Could not translate expression 'Table(consonant).Select(w => (((w + "'") + Invoke(value(System.Func`1[System.String]))) + "'"))' into SQL and could not treat it as a local expression.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Could not translate expression 'Table(consonant).Select(w => (((w + "'") + Invoke(value(System.Func`1[System.String]))) + "'"))' into SQL and could not treat it as a local expression.

Source Error:

Need help with a linq to sql query

  

I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:


Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5
&n

Need Help in LINQ query for group By with chunks of record

  

I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
{
p.CON_ENTRY_POINT,
p.APPTType,
p.CON_CONTAINER_ISSUE_DATE
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
{
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&
h.CON_CONTAINER_ISSUE_DATE == objUnique.CON_CONTAINER_ISSUE_DATE)))
{
obj.Shipper

Query an arraylist of array list using linq

  

I have an array list called Customer. I t holds a list of array list which has the account details.

proj.customer -> collection of arraylist of type customer

proj.customer.account -> collection of arraylist of type account


Account array list holds type account and it has a poperty account balance. How can I query all accounts which has account balance  >100


I tried the following one

var Accountlist= from Account accountdetails in proj.customer.account

where accountid.balance >100

select accountdetails.id

But it is always throwing an error saying cannot cast type customer to type account

Please help


Basic Instincts: Increase LINQ Query Performance

  

Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008


Data Points: Standard Query Operators with LINQ

  

LINQ to Objects and LINQ to Entities have an arsenal of Standard Query Operators that operate on sequences to perform a wide variety of operations.

John Papa

MSDN Magazine March 2008


Need help concatenate varible to a SQL WHERE clause

  

Hey guys,

I looked over the web and couldn't find much help. Can someone help me with my SELECT statement? What I want to do is concatenate a varible to my WHERE clause. This is what I have:

 

myDataAdapter = new SqlDataAdapter("SELECT * FROM details WHERE DetailsID = " + lstBoxCategory.SelectedValue.ToString(), connection);


 

I tried this also but it didn't work:

myDataAdapter = new SqlDataAdapter("SELECT * FROM details WHERE DetailsID = '" + lstBoxCategory.SelectedValue.ToString() + "' ", connection);


 

 


Help with SQL query to Linq (VB.Net)

  

Hello,

I am facing problems converting a relatively complex SQL query to a LinqToSQL statement. There are lots of examples on the web, but none involve 'joins' + 'where' + 'group'

Here is my query I am trying to use Linq for :


select Customers.CustomerID,          
       count(*) as NumOfOrders,         
       sum(Customers.BillingAmount) as BillingAmount,      
       sum(Orders.OrderTotal) as OrderTotal  
From Customers 
left outer join Orders on Customers.CustomerID = Orders.CustomerID
Where Customers.Name = @username	  
      and Customers.LastLoggedIn > @fromdate	  
      and Customers.LastLoggedIn < @throughdate	  
      and Customers.ZipCode = @zipcode 
group by Customers.CustomerID


I need help converting this to a LinqToSql for VB.Net

Thank you very much!


Linq query to DataTable

  

Is there option to convert from Linq.IQueryable to DataTable with some sort of CopyToDataTable() function. I am trying this.
     var row = from p in dc.ProductModels
                  select p;
    DataTable dt = row.CopyToDataTable();

Is there option to convert from Linq.IQueryable to DataTable with some sort of CopyToDataTable() function. I am trying this.


     var row = from p in dc.ProductModels

                  se

Linq query results to List collection

  

Hello

I have a GridView in my page. I want to use below code in code beind. query nothwing DB using linq . add linq query result to new list and then bind list as gridview Datasource

I use below code . But I don't know how to add results to List? Could any one tell me how !


Public Class OrderCity
        Private _name As String
        Private _city As String
        Public Property name As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value

            End Set
        End Property
        Public Property city As String
            Get
                Return _city
            End Get
            Set(ByVal value As String)
                _city = value

            End Set
        End Property
    End Class
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dbe As New SFFAV1DataContext
        Dim latestadsDs = From p In dbe.Products _
                            Select New With {p.Title, p.ProducedCity} _
                             Take (2)

        Dim FilteredList As New List(Of OrderCity)()
        For Each result In latestadsDs
            Dim Menedfcou = Nothing
            FilteredList.Insert(0, Menedfcou)
        Next
     
         GridView1.Da

LINQ query with multiple joins, problem

  

I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
            {  
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new
                                   {
                          

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
Categories: 
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