CODEWITHSUNDEEP
c#linq
kabijoy
kabijoy

Reputation: 303

Linq query for distinct data from a table with where condition

I have an sql statement like this:

select distinct(agent_name)
from properties
where agent_name not in ('null','')

I want the linq query in C# page

Upvotes: 2

Views: 8023

Answers (4)

Paramjot Singh
Paramjot Singh

Reputation: 705

You can use it like this

var forms = db.properties.
                Where(a => a.agent_name == 'null' || a.agent_name == null).
                Select(x => x.agent_name).
                Distinct().
                ToList();

Its equivalent SQL statement, which is designed by LINQ to SQL is

SELECT 
    [Distinct1].[agent_name] AS [agent_name]
    FROM ( SELECT DISTINCT 
        [Extent1].[agent_name] AS [agent_name]
        FROM [dbo].[properties] AS [Extent1]
        WHERE [Extent1].[agent_name] = N'null' OR [Extent1].[agent_name] = N''
    )  AS [Distinct1]

Upvotes: 0

D Stanley
D Stanley

Reputation: 152521

Assuming you're comparing to the string value 'null' like your original query:

List<string> agentNames = db.Properties.Where(p=>p.AgentName != "null" && 
                                        p.AgentName != "")
                                       .Select(p => p.AgentName)
                                       .Distinct()
                                       .ToList();

If you're actually comparing to a null value just change it to:

List<string> agentNames = db.Properties.Where(p=>p.AgentName != null && 
                                        p.AgentName != "")
                                       .Select(p => p.AgentName)
                                       .Distinct()
                                       .ToList();

Upvotes: 5

huMpty duMpty
huMpty duMpty

Reputation: 14460

Try something like this 

   var result =    (from dbo in database.Table
                     where dbo.agent_name!=null and dbo.agent_name !=''
                     select dbo.agent_name).Distinct();

or if you have a list already

  var result = (list.Where(a => a.agent_name!=null && a.agent_name!='').Select(
                a.agent_name)).Distinct();

Upvotes: 0

NinjaNye
NinjaNye

Reputation: 7126

var result = context.Properties.Where(p => p.AgentName != null 
                                        && p.AgentName != "")
                               .GroupBy(p => p.AgentName)
                               .Select(g => g.Key);

Upvotes: 2

Related Questions