CODEWITHSUNDEEP
c#.netmysqllinqlinq-to-sql
JMK
JMK

Reputation: 28059

LINQ to MySQL query returns no result

I am trying to use LINQ to query a MySQL database.

First of all, I have installed the MySQL .NET connector. Then in Visual Studio I create a new Console Application.

Then I click on Data in the toolbar and click on Add New Data Source.

In the Data Source Configuration Wizard I select Database => Dataset => New Connection, I configure my connection parameters, I then select my relevant tables, views etc and hit Finish.

My data source is named Foo, the table I want to query is named Bar.

I then try and query my data table using LINQ like below:

fooDataSet.barDataTable foobar = new fooDataSet.barDataTable();
var results = from data in foobar
              select data;

foreach (var result in results)
{
    Console.WriteLine(result);
};

I'm assuming that this is the equivalent to the following MySQL

SELECT * FROM BAR;

Even though my table (bar) has plenty of data in it, I don't get any data back. When I breakpoint appropriately, and drill down into the Results View of my results variable, it just saysЖ

Enumeration Yielded No Results

enter image description here

Where am I going wrong here?

Upvotes: 2

Views: 1203

Answers (2)

JMK
JMK

Reputation: 28059

I figured this out by repeating the initial steps on a Windows form, dragging the table I want to query from Server explorer onto the Windows Form and then analysing the code.

It turns out I needed first to create an instance of my dataset (Foo), then create a TableAdapter instance for my table (Bar).

Once I have done this, I call the Fill method of my table adapter, passing in the table name in the data set using dot notation.

I then run the AsEnumerable() extension method of GetData() of my table adapter to get an EnumerableRowCollection, which I can then run queries against using LINQ. My code is below:

static void Main(string[] args)
{
    fooDataSet fooDataSet = new fooDataSet();
    fooDataSetTableAdapters.barTableAdapter barTableAdapter = new fooDataSetTableAdapters.barTableAdapter();

    barTableAdapter.Fill(fooDataSet.bar);

    var myDataTable = barTableAdapter.GetData().AsEnumerable();

    var bnl = from results in myDataTable
              select results;

    Console.ReadLine();
}

Side note, on the XSD file created by Visual Studio, every field in every table is set by default to throw an exception if you try to apply criteria to a column with any null values. You need to change the NullValue property of the field in the XSD to either (Empty) or (Null) to avoid this.

Upvotes: 0

cjk
cjk

Reputation: 46415

You are creating a new table and then trying to read data from it (there's nothing in it, its new!)

In LINQ to SQL you would do this:

var results = from data in fooDataset.bar 
              select data; 

foreach (var result in results) 
{ 
    Console.WriteLine(result); 
}

Upvotes: 6

Related Questions