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

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

Convert SQL to LinQ help

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :ASP.Net
Hello,I want to run sql query to select list on accounts with automatic payment, open invoices of each account (append as string), count of invoices, total amount of invoices.There are 2 tables: tblAccount with accountNo and autopayment information.tlbInvoices with invoiceID, accountNo, amount.SQL query on database is like thisselect accountNo,[IC_invoices].[dbo].[ConcatOpenInvoice](accountno) as invoices,sum(totalAmount) as amount, count(Reference) as NumberofInvoices  from [IC_invoices].[dbo].tblInvoice  where accountNo in(select distinct accountno from [IC_invoices].[dbo].[tblAccount] where autopayment='yes')  and paystatus is null  group by accountNoConcatOpenInvoice is a function to append invoice ID of all open invoices under one account.Is it possible to convert this sql to linq.Thank you.

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

Convert LINQ to Int



var query = from p in dc.Patients
                        select p.PAge;

Can i get PAge value in an any int like....

int PAge = query.Single();

I'm trying Convert.ToInt32(-----------------)

but this give me Error on run time


i need some 1 help plz

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


How to convert SQL concatenate to LINQ concatenate query?



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.




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

convert the where clause to linq



how do you convert the WHERE clause of this statement to Linq ??

Where (@product is null or A.name Like @product )
  and (@category is null or B.name Like @category )
  and (@supplier is null or C.NameRef Like @supplier )

Select A.Name,B.name,C.NameRef

From Table1 A

Inner join Table2 B on A.Id=B.subId

Inner join Table3 C on B.ID=C.subID

Where (@product is null or A.name Like @product )

  and (@category is null or B.name Like @category )

  and (@supplier is nul

cannot convert string to bool error in LINQ


hi guys,

this is my code:

DataClassesDataContext db = new DataClassesDataContext();
        item it = new item();

        var items = from p in db.items
                    where p.id = TextBox1.Text
                    select p;

        GridView1.DataSource = items;

an error message at line 5 says:

  Cannot implicitly convert type 'string' to 'bool'

the id attribute on the items table is of varchar(50) type. and the text box too is of string type.

so why does it says conversion between boolean and string problem?

thank you all.

dropdown list fill with Linq and convert string to datetime and ordering and stored procedure call.


I wanted to share this more than anything,  it took me most of the morning searching and finding various post on various sites but I got it figured out and working.  It might be better coded, but it is a start for some one trying to understand how linq and ASP.Net things work.

What I have is a database table with a datetime in string format and a stored procedure which returns all the data.
What I want to do is load a dropdown list with MonthName and Year for a selection choice on generating monthly report.

The stored procedure is in a Linq to SQL class dbml and the connection string is dynamic, i.e., made througha call to another class.

here is the code.  Enjoy understanding how it works.

// Miscellaneous Details are filled in for helping you get the big picture.
// Some of the using statements are for other things in the code behind, 
// but I left it in so don't get confused,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections.ObjectModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using MYProject.LinqtoSQLStuff;

namespace MYProject.ChooseReportPage
	 public partial class TheReportPage : System.Web.UI.Page  
	     protected void Page_Load(object sender, EventArgs e)


Playing with Linq grouping: GroupByMany?

One of its features is grouping. Many people understand grouping like it is defined in Sql. Linq is implementing grouping quite the same way. Let's discover this syntax and how to make consecutive groups easier.

LINQ : Implementing IN and NOT IN

I got tried of typing

var result = from s in source
where items.Contains(s)
select s;and so I implemented the IN and NOT IN methods as extension methods:

101 LINQ Samples Tutorials

101 LINQ Example with sample code snippets....This sample prints each element of an input integer array whose value is less than 5. The sample uses a query expression to create a new sequence of integers and then iterates over each element in the sequence, printing its value

Linq: how to share parameters between lambda expressions ?

When using Linq to objects, you will quickly feel the need to pass some parameters from a method to another but it's not so easy because each Linq method is not calling the following one. In a Linq sequence, each method is using the result computed by the previous one. So, local contexts are not visible from one method to another.
The compiler is using two technical different ways to let parameters go out of a method.

Linq to Sql CompiledQuery container

Ok now let's go. Here is just a little trick but with some interesting patterns that could be useful in some other contexts not connected to Linq to Sql.

When using Linq expressions, like with Linq to Sql, translating the expression into something else (sql for example) is taking time and resources. Sometimes it's negligible, sometimes not...

LINQ to SQL Paging using GridView in C# and ASP.NET 3.5

This tutorial will show you how we can extend the LINQ to SQL Class and create methods that will allow us to page data from a SQL database. C# version.

What is Lambda Expressions In LINQ

A lambda expression is an anonymous function that can contain expressions and statements, and can be used to create delegates or expression tree types.

All lambda expressions use the lambda operator =>, which is read as "goes to". The left side of the lambda operator specifies the input parameters (if any) and the right side holds the expression or statement block. The lambda expression x => x * x is read "x goes to x times x." This expression can be assigned to a delegate type as follows:

How to create 3 tier application using LINQ

As you know that in 3 tier architecture there are three layers

User interface layer. (Is our Form in Windows application and .aspx page in Web application)
Data Access layer. (Which provides interface between Business logic layer and Database)
Business Logic layer.(Which stores your application logic)

Building a LINQ Provider

Throughout this article, we will build a simple IQueryable provider similar to the LINQ to SQL provider demonstrating what is needed to build a custom provider that is capable of executing LINQ queries and performing create, update and delete operations.

LINQ to SQL Performance Considerations

As developers we have a wide array of tools at our disposal. One of the most time consuming, but most beneficial tasks we must undertake is to determine which of those tools to use for a given situation.
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