.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 the where clause to linq

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


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

View Complete Post

More Related Resource Links

LINQ to XML - Example on how to replicate the SQL "IN" clause in LINQ


Can anyone please show me the example as how to replicated the functionality same as SQL "IN" clause using LINQ to XML ?

I have an XML file that I want to filter by passing multiple values to attribute parameters.

Any help will be greately appreciated

Dynamic where clause - Linq or SPQuery?

Has anyone tried to build a dynamic where clause in Linq to Sharepoint (i.e. search criterias are being modified at run time)? I think SPQuery/CAML is a better choice for this one..

Use of PATINDEX function in select clause With Linq

hello       i have an problem to use of PATINDEX function in select clause with Linq.      if anyone have any clue about it then please update me its urgent.Thanks In Advance.

Convert SQL to LinQ help

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.

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

Linq Where Clause (on class that has an [array])

Hi There.I have the following query that return the below class type.var results = from r in _returnListOfItem(XMLFile)                       select r;   // Image BluePrint    public class OneImageBluePrint    {        public int UniqueID { get; set; }        public string VirtualFilePath { get; set; }        public string URL { get; set; }        public string Title { get; set; }        public string Subject { get; set; }        public string[] Keywords { get; set; }        public string Author { get; set; }        public string Collection { get; set; }        public string DateAdded { get; set; }        public string Copyright { get; set; }        public int PriceRange { get; set; }        public string LocationPlace { get; set; }        public string LocationCity { get; set; } &nbs

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

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.

Linq Query with dynamic where clause i.e filter with dynamic no of filter columns



I want a linq query with dynamic where clause.

Following is explanation of problem:


Following line returns me n no of rows.

IEnumerable<XElement>  xeleRows = (from xele in xdocFile.Root.Elements(strCtlLookUpXMLNode) select xele);

structure of xeleRows will be like:













Now I want to get one row from N Number of rows on basis of filter criteria that can be dynamic, the no of columns in filter criteria will vary at run time.

Anybody know shows to achieve this using Linq. Please guide.



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