Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Query In LINQ

Hello

I have a requirement in LINQ. Ineed to write a query which can seach a string in multiple columns of the same table. I have written a equivatent SQL query and even tried to convert it thru LINQER. But the keywords PATINDEX and COLASCE are not supported. Please helop

SOS

TIA
Else IF @TableName = 'EMN' 	
			select EM.emnid,EM.emnnumber,EM.location from emn_master EM
			left outer join dbo.project_master PM on EM.Projectid = PM.Projectid 
			left outer join dbo.manifest_master MM on EM.Manifestid = MM.Manifestid 
			WHERE PATINDEX('%' + @pattern + '%',COALESCE(EM.emnnumber,'') + '|' +
			COALESCE(PM.projectnumber,'') + '|'+ 
			COALESCE(MM.Manifestnumber,''))>0

Open in new window

0
ExpertHelp79
Asked:
ExpertHelp79
  • 15
  • 13
1 Solution
 
DhaestCommented:
What exactly do you want to do ?

Perform a query that searches in multiple columns for a specific value (string) ?

Why don't you use a or-statement ?
0
 
ExpertHelp79Author Commented:
yes that can
one of the ways that i have not tried. But right now i have written a stored procedure which has multiple IFs but when i sen parameter as Table the SP returns the values of the first IF block

Say i have the stored procedure below and when i pass the parameters

TableName : EMN
pattern : TEST

This runs fine and send me the correct values

bur when i pass the parameters as
TableName : Company
pattern : TEST

in Linq resultset still i get the EMN feilds which are all null.

When i run the SP explicitly it is running correct but when i call the SP from LINQ i get the wierd issue.

please help.

USE [ESPRAT2005]
GO
/****** Object:  StoredProcedure [dbo].[SearchViews]    Script Date: 05/06/2011 16:25:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SearchViews] 
	@TableName varchar(50),
	@pattern varchar(255)
AS
BEGIN
	
	SET NOCOUNT ON;

		IF @TableName = 'EMN' 	
			select EM.emnid,EM.emnnumber,EM.location from emn_master EM
			left outer join dbo.project_master PM on EM.Projectid = PM.Projectid 
			left outer join dbo.manifest_master MM on EM.Manifestid = MM.Manifestid 
			WHERE PATINDEX('%' + @pattern + '%',COALESCE(EM.emnnumber,'') + '|' +
			COALESCE(PM.projectnumber,'') + '|'+ 
			COALESCE(MM.Manifestnumber,''))>0		


		IF @TableName = 'Company'	
			select CM.companyid,CT.companytypename as companytypename,CM.companycode,CM.companyname,CM.address1,
					CM.phone,CM.contactperson,CM.emailid,CM.website
			from company_master CM
			Left Outer Join company_types CT on CM.companytypeid = CT.companytypeid  
			WHERE PATINDEX('%' + @pattern + '%',COALESCE(companycode,'') + '|' +
			COALESCE(CM.companyname,'') + '|'+ 
			COALESCE(CM.address1,'')+ '|' + 
			COALESCE(CM.contactperson,'')+ '|' + 
			COALESCE(CM.emailid,'')+ '|' + 
			COALESCE(CM.website,''))>0

END

Open in new window

0
 
DhaestCommented:
So if I understand it correctly, you just want to retrieve the data like now through a stored procedure and than run a linq-query ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ExpertHelp79Author Commented:
yes ... but i am getting the issue which i described above... is it something the SP is not getting added properly in the dbml file
0
 
ExpertHelp79Author Commented:
i am trying the OR you suggested but i am getting error


Error Compiling Expression: Error Compiling Expression: 'int' does not contain a definition for 'Contains' and no extension method 'Contains' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?)
0
 
ExpertHelp79Author Commented:

from em in db.Emn_master
where
  em.Emnid.Contains("test") ||
  em.Emnnumber.Contains("test") ||
  em.Location.Contains("test")
select new {
  em.Emnid,
  em.Emnnumber,
  em.Projectid,
  em.Clientid,
  em.Location,
  em.Emndate,
  em.Containerid,
  em.Manifestid,
  em.Clientfrom,
  em.Clientto,
  em.Attention,
  em.Equiptransferfrom,
  em.Equiptransferto,
  em.Perposeoftransfer
}

Open in new window

0
 
DhaestCommented:
In what object do you retrieve the data from your stored procedure ?
0
 
ExpertHelp79Author Commented:
var result = (from c in ed.SearchViews("EMN", txtCustom.Text.Trim())
                              select c);

SearchViews is the SP
0
 
DhaestCommented:
And now you want to search in your var result  ?

But you don't have the same columns in your result (they are depending of your parameters)
0
 
ExpertHelp79Author Commented:
so if in case it wrong please guide me....

when i pass the parameter as EMN and 'test' and run my program the output is perfect ... but when i put it as Company and 'test' it returns 0 as it is returning the columns of EMN table
0
 
DhaestCommented:
If you run the stored procedure on sql-server, do you see 2 different results (other columns)  appear with your parameters ?

0
 
ExpertHelp79Author Commented:
no just one result
0
 
DhaestCommented:
>> no just one result

off course one result, but different columns ?

exec SearchViews 'EMN', 'TEST' should give following columns: EM.emnid,EM.emnnumber,EM.location
(with or without results)

exec SearchViews 'Company', 'TEST' should give following columns: .companyid,CT.companytypename as companytypename,CM.companycode,CM.companyname,CM.address1,
                              CM.phone,CM.contactperson,CM.emailid,CM.website
(with or without results)
0
 
ExpertHelp79Author Commented:
exec SearchViews 'EMN', 'TEST' should give following columns: EM.emnid,EM.emnnumber,EM.location
(with results)

exec SearchViews 'Company', 'TEST' should give following columns: .companyid,CT.companytypename as companytypename,CM.companycode,CM.companyname,CM.address1,
                              CM.phone,CM.contactperson,CM.emailid,CM.website
(with results)
0
 
DhaestCommented:
So the query returns data for both scenarios ?

In that case, can you post your linq-query you tried after this one:

var result = (from c in ed.SearchViews("EMN", txtCustom.Text.Trim())
                              select c);

0
 
ExpertHelp79Author Commented:
this is only the linq query i bind 'result' to the data grid
var result = (from c in ed.SearchViews("EMN", txtCustom.Text.Trim())
                              select c);

                

                GridView1.DataSource = result;
                GridView1.DataBind();
                lblCount.Text = GridView1.Rows.Count.ToString() + " records found";

Open in new window

0
 
DhaestCommented:
I think that the gridview is still holding the old columns.

Can you try this piece of code

GridView1.DataSource = null;
GridView1.DataBind();
GridView1.DataSource = result;
GridView1.DataBind();

OR

gridview1.Columns.Clear();
GridView1.DataSource = result;
GridView1.DataBind();
0
 
ExpertHelp79Author Commented:
i can try .... but for COMPANY and EMN they are altogether different gridviews
0
 
ExpertHelp79Author Commented:
no same issue ......
0
 
DhaestCommented:
I'm sorry, but I'm lost right now .....

You have 2 datagrids ?
gridview1 is linked to var result = (from c in ed.SearchViews("EMN", txtCustom.Text.Trim()) select c);

and another one is linked to var result = (from c in ed.SearchViews("Company", txtCustom.Text.Trim())
                              select c);

The query's return the right information ?
(see in debug before binding in gridview - so your var result)

What is the exact problem that you still have ?
0
 
ExpertHelp79Author Commented:
You have 2 datagrids  
gridview1 is linked to var result = (from c in ed.SearchViews("EMN", txtCustom.Text.Trim()) select c);
This returns correct

and another one is linked to var result = (from c in ed.SearchViews("Company", txtCustom.Text.Trim())
                              select c);
This still returns columns of the above result .

this is the issue and i am confused.

Now when i see the Dbml.cs then i see that specifically search view returns Emn columns .. we cannot change as it is auto generated
public partial class SearchViewsResult
{
	
	private int _emnid;
	
	private string _emnnumber;
	
	private string _location;
	
	public SearchViewsResult()
	{
	}
	
	[Column(Storage="_emnid", DbType="Int NOT NULL")]
	public int emnid
	{
		get
		{
			return this._emnid;
		}
		set
		{
			if ((this._emnid != value))
			{
				this._emnid = value;
			}
		}
	}
	
	[Column(Storage="_emnnumber", DbType="NVarChar(50)")]
	public string emnnumber
	{
		get
		{
			return this._emnnumber;
		}
		set
		{
			if ((this._emnnumber != value))
			{
				this._emnnumber = value;
			}
		}
	}
	
	[Column(Storage="_location", DbType="NVarChar(MAX)")]
	public string location
	{
		get
		{
			return this._location;
		}
		set
		{
			if ((this._location != value))
			{
				this._location = value;
			}
		}
	}
}

Open in new window

0
 
DhaestCommented:
Are you using the entity framework ?

Perhaps you should use 2 different stored proc's
0
 
ExpertHelp79Author Commented:
i am using the below workaround now ... but its not generalized. butr still working for now.

I will still like to know why i cannot use one SP ... because it is not feasible to write a seperate SP for each view when i have to implement around 20.
var query = from em in ed.emn_masters
                            where
                              em.emnnumber.Contains(txtCustom.Text.Trim()) ||
                              em.location.Contains(txtCustom.Text.Trim())
                            select new
                            {
                                emnid = em.emnid,
                                emnnumber = em.emnnumber,                                
                                location = em.location
                                
                            };

                GridView1.DataSourceID = string.Empty;
                GridView1.DataSource = null;
                GridView1.DataBind();

                GridView1.AllowPaging = false;

                GridView1.DataSource = query;
                GridView1.DataBind();

Open in new window

0
 
DhaestCommented:
When is this class public partial class SearchViewsResult generated ?
0
 
DhaestCommented:
When is this class public partial class SearchViewsResult generated ?

I think that the generator only creates the columns from the first if-script and therefore you can't use it like the way you want !
0
 
ExpertHelp79Author Commented:
yes...... it is created when i drag the sp on the dbml
0
 
DhaestCommented:
>> yes...... it is created when i drag the sp on the dbml

In that case I can't help you further. I never do that (because I feel that I loose control over my classes, data, ...).

You have now such an example where you drag/drop it, resulting that it only shows the field of the first query.

The alternative that you can do to make sure that you always have the field is to adjust your sp so that every field is in your firstquery (returned as null)
USE [ESPRAT2005]
GO
/****** Object:  StoredProcedure [dbo].[SearchViews]    Script Date: 05/06/2011 16:25:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SearchViews] 
	@TableName varchar(50),
	@pattern varchar(255)
AS
BEGIN
	
	SET NOCOUNT ON;

		IF @TableName = 'EMN' 	
			select EM.emnid,EM.emnnumber,EM.location 
null as companyid, null  as companytypename, null  as companycode, null  as companyname, null  as address1,
null  as phone, null  as contactperson, null  as emailid, null  as website
from emn_master EM
			left outer join dbo.project_master PM on EM.Projectid = PM.Projectid 
			left outer join dbo.manifest_master MM on EM.Manifestid = MM.Manifestid 
			WHERE PATINDEX('%' + @pattern + '%',COALESCE(EM.emnnumber,'') + '|' +
			COALESCE(PM.projectnumber,'') + '|'+ 
			COALESCE(MM.Manifestnumber,''))>0		


		IF @TableName = 'Company'	
			select CM.companyid,CT.companytypename as companytypename,CM.companycode,CM.companyname,CM.address1,
					CM.phone,CM.contactperson,CM.emailid,CM.website	
from company_master CM
			Left Outer Join company_types CT on CM.companytypeid = CT.companytypeid  
			WHERE PATINDEX('%' + @pattern + '%',COALESCE(companycode,'') + '|' +
			COALESCE(CM.companyname,'') + '|'+ 
			COALESCE(CM.address1,'')+ '|' + 
			COALESCE(CM.contactperson,'')+ '|' + 
			COALESCE(CM.emailid,'')+ '|' + 
			COALESCE(CM.website,''))>0

END

Open in new window

0
 
leonstrykerCommented:
I want to see the work around.
0
 
ExpertHelp79Author Commented:
ID: 35706259 please follow this
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 15
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now