CODEWITHSUNDEEP
c#linq
sonlexqt
sonlexqt

Reputation: 6469

ADO.NET - How to select multiple rows from a table ? (and count the number of the rows)

I'm populating an C# Winform application with ADO.NET. Can anybody tell me how to select multiple rows from a table? I already know a way to select only 1 row from a table, it is, for example: 

private WorldCupEntities wce = new WorldCupEntities();
FOOTBALL_TEAM ft = wce.FOOTBALL_TEAM.Single(x => x.ID.Equals(aKnownIDVariable));

And, by the way, how to count the number of the rows after I have selected them ?

Thanks you guys so much in advanced !

Upvotes: 0

Views: 949

Answers (5)

catfood
catfood

Reputation: 4331

Presuming that the WorldCupEntities class is your data model...

using System.Linq;

private WorldCupEntities wce = new WorldCupEntities();
var ft = wce.FOOTBALL_TEAM.Where(x => x.ID.Equals(aKnownIDVariable));
int count = ft.Count();

The .Where extension method gives you the entities matching the condition you wanted, in an IEnumerable of the football-team type.

Counting them is easy with LINQ, because .Count() is defined as a LINQ extension method too.

You can read a lot more in the MSDN documentation on query operators: http://msdn.microsoft.com/en-us/library/bb397927.aspx

Upvotes: 0

Neil Smith
Neil Smith

Reputation: 2565

List<FOOTBALL_TEAM> fts = wce.FOOTBALL_TEAM.Where(x => x.ID.Equals(aKnownIDVariable)).ToList();

to get many where Id equals. Might want to change the query criteria though since I'm guessing ID is unique. The ToList gives you a list and getting the count of a list is as easy as list.Count.

If you wanted to get just the count where the query's condition is met:

int count = wce.FOOTBALL_TEAM.Count(x => x.ID.Equals(aKnownIDVariable));

And now for an explanation why Where didn't originally work for you. Where is going to return a IQueryable<FOOTBALL_TEAM>. You were trying to set that to an instance of FOOTBALL_TEAM instead of the correct type. Instead you could use var or the correct type IQueryable<FOOTBALL_TEAM> or convert the result to a list using ToList like I did above.

Upvotes: 1

Robin Rizvi
Robin Rizvi

Reputation: 5183

For selecting all rows:

private WorldCupEntities wce = new WorldCupEntities();
var ft = from r in wce.FOOTBALL_TEAM select r;

or

private WorldCupEntities wce = new WorldCupEntities();
var allRows = wce.FOOTBALL_TEAM.ToList();

For selecting multiple rows (zero or more):

private WorldCupEntities wce = new WorldCupEntities();
var multipleRows = wce.FOOTBALL_TEAM.Where(x => x.ID.Equals(aKnownIDVariable)).ToList();

For the count

private WorldCupEntities wce = new WorldCupEntities();
var count = wce.FOOTBALL_TEAM.Where(x => x.ID.Equals(aKnownIDVariable)).Count();

or 

private WorldCupEntities wce = new WorldCupEntities();
    var multipleRows = wce.FOOTBALL_TEAM.Where(x => x.ID.Equals(aKnownIDVariable)).ToList();
var count = multipleRows.Count;

Upvotes: 0

field_b
field_b

Reputation: 698

Use implicit typing with the var keyword:

var ft = wce.FOOTBAL_TEAM.Where(x=>x.ID==(aKnownIDVariable));

Then count the results:

int count = ft.Count();

Upvotes: 0

humam
humam

Reputation: 191

To select all records, try this

private WorldCupEntities wce = new WorldCupEntities();
var listAll = from item in wce.FOOTBALL_TEAM
                  select item;

or by using lambda expressions like this:

private WorldCupEntities wce = new WorldCupEntities(); 
var list = wce.FOOTBALL_TEAM.Where(x => x.ID >= aKnownIDVariable);

Upvotes: 0

Related Questions