linq versus BindingSource.

[Mr. X.]

Hello,

I think that BindingSource is old fashion attitude.
I use filter, and I think that on large table there will be performance
problems (because before assign value to filter, I need to retrieve the
table data).

I want to change my code using linq instead.
Before I change 100,000 lines of code, I need to know whether the linq
overcome the performance problems,
and is linq indeed is the best way that good for convenience and also good
for performance?
Is there any other way to retrieve from data fast ?
If not, what else can I use instead of using BindingSource?

Also :
Can I write into database via linq? (that is must).

Thanks

 

[Jason Keats]

Hello,

I think that BindingSource is old fashion attitude.
I use filter, and I think that on large table there will be performance
problems (because before assign value to filter, I need to retrieve the
table data).

I want to change my code using linq instead.
Before I change 100,000 lines of code, I need to know whether the linq
overcome the performance problems,
and is linq indeed is the best way that good for convenience and also
good for performance?
Is there any other way to retrieve from data fast ?
If not, what else can I use instead of using BindingSource?

Also :
Can I write into database via linq? (that is must).

Thanks

When you have a question it's a good idea to supply enough information
about your application & architecture for someone to provide a sensible
answer. Do you want us to guess the best answer?

Is this a Windows Forms, WPF, Silverlight or ASP.NET application? In
other words, what are you binding to?
Are you binding datatables or custom collections?
What database are you using?
Are you interested in Linq to Datasets, Linq to SQL, Linq to Objects,
Linq to XML, or Linq to Entities?
Why do you want to change your code? Is it a performance issue? Are you
worried about scalability? Are you trying to make your code more
maintainable/extensible?

In general, Linq (to SQL) will not overcome any performance problems if
you're already using the fastest method of data access.

Please provide more information.

 

[Mr. X.]

VS 2008. C#. Windows 7.
Work with database (Several database drivers supported : My-Sql, Oracle,
Sql-Server, OleDB, ODBC)
Now I link some BindingSources to some DataTables.
When using filter, I need before to do : loading table.
I want to use something more modern then that?
For fast : I need a feature that parse to a real sql statement, and perform
that on database.
(When I do : Filter, there is no real sql-statement behind, but retrieving
from the memory).
For my question :
Does linq that work with dataTable do behind code : real sql-statement to
database, or just do some manipulation with memory?

Thanks

 

[Mr. X.]

I use also DataAdapter.
Please, lead me to a way, where I can read/write to database? Something
modern, fast retrieving from DB.

Thanks

 

[Jason Keats]

VS 2008. C#. Windows 7.
Work with database (Several database drivers supported : My-Sql, Oracle,
Sql-Server, OleDB, ODBC)
Now I link some BindingSources to some DataTables.
When using filter, I need before to do : loading table.
I want to use something more modern then that?
For fast : I need a feature that parse to a real sql statement, and
perform that on database.
(When I do : Filter, there is no real sql-statement behind, but
retrieving from the memory).
For my question :
Does linq that work with dataTable do behind code : real sql-statement
to database, or just do some manipulation with memory?

If you want to use a variety of databases then you can't use Linq to
SQL, which is a simple ORM (object-relational mapper) for SQL Server.

Linq to Entities is used with Entity Framework - a more powerful
replacement for Linq to SQL. Its trying to be like NHibernate.

If you're used to using datatables, then moving to an ORM is a big step
- and would probably require a large rewrite of your code.

Linq to Objects allows you to do SQL-like selects/sorts/etc. on
in-memory custom collections - which you would have if using an ORM.

Linq to Datasets does a similar thing with (you guessed it) datasets (so
it's a bit like a filter).

There is nothing wrong with using datatables and filters - as long as
you don't try to load too much data into memory.

Why do you "want to use something more modern"?

 

[Jason Keats]

I use also DataAdapter.
Please, lead me to a way, where I can read/write to database? Something
modern, fast retrieving from DB.

Thanks

DataReader - fastest way to read data. (free)
NHibernate - best open source ORM. (free)
LLBLGen Pro - good commercial ORM/code generator. (not free)

 

[Mr. X.]

O.K.
Maybe linq is not the best I should use
(because it supports only sql-server)
I don't care of the efforts that's take.
I need a solution (and I thought, that linq was :
1. good for non sql-server databases also.
2. parse the sql (with the whole statement - where, order by, etc.) and send
it to the database server to be execute, so it is not real filter to memory.
I am a little disappointed of the fact that linq does the final thing in
memory, and doesn't send the parsed sql to datbase
(This is what fast to me. That are things that can be controlled. I mean
when I send the sql-statement, I can control the performance by the
database-level : good queries, good indexes, etc.)

What are the alternatives?

Thanks

 

[Mr. X.]

Also, I don't know much about techniques of writing/reading to database, but
only I know :
linq, dataAdapter, command, connection, DataReader, and manipulation through
BindingSource.
Maybe there is more - something new that can read/write to any database, and
also send the native-sql, when there is a request to retrieve data from
database.

Thanks

 

[Felix Palmen]

Can I write into database via linq? (that is must).

Maybe it's pointing out the obvious but -- no, you can't. Linq is just a
kind of "functional" part of some CLR languages (here, C#) that allows
to /query/ data from objects.

But, there are frameworks that map database tables to objects (I like
the entitiy framework) that are
- queryable using linq (Linq to Entities)
- state-tracked by a context, so you can save changes to these objects
by calling a method on the context.

That's probably what you want, but it's not "writing via linq".

Regards, Felix

 

[Mr. X.]

OK,
but I didn't understand about state-tracked.
For linq - Jason told me that linq is good only for sql-server.
I need :
1. An object or attitude, that do real parsing (not filtering. If I give the
"where" statement separately - I want that the object will build the whole
sql-statement).
2. A way I can write to database.
3. Not much sql-code inside (linq is OK, but it dismiss the filtering, and
only fits to sql-server. I can write by myself with no problem, but I need
something generic).

Thanks

Can I write into database via linq? (that is must).

Maybe it's pointing out the obvious but -- no, you can't. Linq is just a
kind of "functional" part of some CLR languages (here, C#) that allows
to /query/ data from objects.

But, there are frameworks that map database tables to objects (I like
the entitiy framework) that are
- queryable using linq (Linq to Entities)
- state-tracked by a context, so you can save changes to these objects
by calling a method on the context.

That's probably what you want, but it's not "writing via linq".

Regards, Felix

--
Felix Palmen (Zirias) + [PGP] Felix Palmen <[email protected]>
web: http://palmen-it.de/ | http://palmen-it.de/pub.txt
my open source projects: | Fingerprint: ED9B 62D0 BE39 32F9 2488
http://palmen-it.de/?pg=pro + 5D0C 8177 9D80 5ECF F683

 

[Jason Keats]

O.K.
Maybe linq is not the best I should use
(because it supports only sql-server)
I don't care of the efforts that's take.
I need a solution (and I thought, that linq was :
1. good for non sql-server databases also.
2. parse the sql (with the whole statement - where, order by, etc.) and
send it to the database server to be execute, so it is not real filter
to memory.
I am a little disappointed of the fact that linq does the final thing in
memory, and doesn't send the parsed sql to datbase
(This is what fast to me. That are things that can be controlled. I mean
when I send the sql-statement, I can control the performance by the
database-level : good queries, good indexes, etc.)

What are the alternatives?

You can continue to use ADO.NET directly, or you can use an
Object-Relational Mapper (which still uses ADO.NET in the background).

If you use ADO.NET directly, then you must decide whether to code to
specific data classes or to their interfaces. If you code to interfaces
(eg IDbConnection, IDbCommand, IDbDataParameter, IDbDataAdapter,
IDataReader, IDbTransaction, etc.), there will be less code to change if
you switch database providers.

Data access code is repetitive, so you might want to use a code generator...

Microsoft: use T4 templates within Visual Studio
Open-source: MyGeneration
Commercial: CodeSmith

Make sure any generated code is in partial classes - so regenerating
doesn't delete your own code.

The main problem with using DataTables and DataSets is that they tend to
be used throughout the whole application - even in business and
presentation layers. This ties your whole application to the future of
ADO.NET.

If you want to avoid that, then use a DataReader to fill your
classes/collections and generate code to insert, update and delete your
data. Of course, an ORM does all that for you!

If you want to use an ORM with a Linq provider, then...

Microsoft: Entity Framework with Linq to Entities
Open-source: NHibernate with Linq to NHibernate
Commercial: LLBLGen Pro comes with Linq for NHibernate and Entity Framework

So you can use Linq to access your data! However, a Linq provider is
just another layer on top of everything else (that may contain bugs) -
and it's not going to be any quicker than using ADO.NET directly.

You can also use an ORM without a Linq Provider and still use Linq to
Objects to query/sort your collections in memory.

 

[Jason Keats]

For linq - Jason told me that linq is good only for sql-server.

No, I didn't say that.

I said "Linq to SQL" is a simple Microsoft ORM that only works with SQL
Server.

If you want to use other databases, then use can use Microsoft's Entity
Framework, or a third party ORM.

 

[Mr. X.]

Quicker for me is enough when the code behind is translated to real sql (the
whole select statement).
I use DbProviderFactory to handle several database types, and manage in code
doing this properly with different databases types.
If ORM solve this (does real sql code. Some objects should do that ...), so
I would like some samples to start with, please.
The only issue, that I need to do "Filter". To add the where statement in
the existing sql, is not big dill (I would prefer doing that via an object,
that doing that), so if there is no object for that, that do so, I should
write some methods by myself.

Thanks

 

[Jason Keats]

Quicker for me is enough when the code behind is translated to real sql
(the whole select statement).
I use DbProviderFactory to handle several database types, and manage in
code doing this properly with different databases types.
If ORM solve this (does real sql code. Some objects should do that ...),
so I would like some samples to start with, please.
The only issue, that I need to do "Filter". To add the where statement
in the existing sql, is not big dill (I would prefer doing that via an
object, that doing that), so if there is no object for that, that do so,
I should write some methods by myself.

I don't really understand what you're asking, however if you want to
make a start on learning an ORM, then go to: http://nhforge.org/

 

[Felix Palmen]

1. An object or attitude, that do real parsing (not filtering. If I give the
"where" statement separately - I want that the object will build the whole
sql-statement).
2. A way I can write to database.
3. Not much sql-code inside (linq is OK, but it dismiss the filtering, and
only fits to sql-server. I can write by myself with no problem, but I need
something generic).

That's all quite vague and unclear ... I now understand it like this:

You want something that "translates" linq expressions to SQL statements,
so the database isn't bothered delivering rows that won't be used. Is
that what you are talking about?

The entity framework does that -- you can construct a linq expression on
an entity set and as long as you don't call .ToArray(), .ToList() or
just try to access a member of the ObjectQuery, it isn't executed for
real. Be careful with that for example when you try to construct an
expression in a loop -- loop iterating variables won't work as expected
because they are only evaluated when the framwork actually translates
your ObjectQuery to SQL (so they will have the value of the last loop
iteration).

As I said, for writing to the database, the entity framework applies
state tracking to all entity objects. You can just call SaveChanges() on
the entity context when you're done modifying your objects.

Regards, Felix

 

[Mr. X.]

I didn't understand whether entity framework translate to sql (linq does not
do that) , or just do filtering on an sql (and also, is there any function
that resolve the whole sql statement).
I would like a sample, please for selecting and for writing.

Thanks

1. An object or attitude, that do real parsing (not filtering. If I give
the
"where" statement separately - I want that the object will build the
whole
sql-statement).
2. A way I can write to database.
3. Not much sql-code inside (linq is OK, but it dismiss the filtering,
and
only fits to sql-server. I can write by myself with no problem, but I
need
something generic).

That's all quite vague and unclear ... I now understand it like this:

You want something that "translates" linq expressions to SQL statements,
so the database isn't bothered delivering rows that won't be used. Is
that what you are talking about?

The entity framework does that -- you can construct a linq expression on
an entity set and as long as you don't call .ToArray(), .ToList() or
just try to access a member of the ObjectQuery, it isn't executed for
real. Be careful with that for example when you try to construct an
expression in a loop -- loop iterating variables won't work as expected
because they are only evaluated when the framwork actually translates
your ObjectQuery to SQL (so they will have the value of the last loop
iteration).

As I said, for writing to the database, the entity framework applies
state tracking to all entity objects. You can just call SaveChanges() on
the entity context when you're done modifying your objects.

Regards, Felix

--
Felix Palmen (Zirias) + [PGP] Felix Palmen <[email protected]>
web: http://palmen-it.de/ | http://palmen-it.de/pub.txt
my open source projects: | Fingerprint: ED9B 62D0 BE39 32F9 2488
http://palmen-it.de/?pg=pro + 5D0C 8177 9D80 5ECF F683

 

[Felix Palmen]

I didn't understand whether entity framework translate to sql (linq does not
do that) , or just do filtering on an sql (and also, is there any function
that resolve the whole sql statement).
I would like a sample, please for selecting and for writing.

Again: linq doesn't know anything of SQL or databases. It is a query
language for objects! You can query your arrays or lists of objects
using linq.

Together with an object-relational mapper (ORM) like the entity
framework (or the much simpler linq-to-sql), you can USE linq to query
from a database. I don't know much of linq-to-sql, never used it, but
the entity framework implements IQueryable itself on "entity
collections" in a way that a linq expression tree is translated to SQL
the instant you try to access an object in the result set. So you end up
with an sql statement that just fetches those objects needed for your
linq expression. AFAIK, you can't get the generated SQL through some
API, though. You'll have to trust the entity framework to do the right
thing (or verify it using some other tracing method).

 

[Mr. X.]

As I understood from this thread, there isn't any feature, that translate
the object.where to a real where-statement in sql.
(Even behind code).

Nevertheless, I need an example for this attitude (something that works with
database), please.

Thanks

I didn't understand whether entity framework translate to sql (linq does
not
do that) , or just do filtering on an sql (and also, is there any
function
that resolve the whole sql statement).
I would like a sample, please for selecting and for writing.

Again: linq doesn't know anything of SQL or databases. It is a query
language for objects! You can query your arrays or lists of objects
using linq.

Together with an object-relational mapper (ORM) like the entity
framework (or the much simpler linq-to-sql), you can USE linq to query
from a database. I don't know much of linq-to-sql, never used it, but
the entity framework implements IQueryable itself on "entity
collections" in a way that a linq expression tree is translated to SQL
the instant you try to access an object in the result set. So you end up
with an sql statement that just fetches those objects needed for your
linq expression. AFAIK, you can't get the generated SQL through some
API, though. You'll have to trust the entity framework to do the right
thing (or verify it using some other tracing method).

--
Felix Palmen (Zirias) + [PGP] Felix Palmen <[email protected]>
web: http://palmen-it.de/ | http://palmen-it.de/pub.txt
my open source projects: | Fingerprint: ED9B 62D0 BE39 32F9 2488
http://palmen-it.de/?pg=pro + 5D0C 8177 9D80 5ECF F683

 

[Felix Palmen]

As I understood from this thread, there isn't any feature, that translate
the object.where to a real where-statement in sql.
(Even behind code).

You seem to ignore my text. The entity framework (for example) does
exactly that. It's just NOT a feature of "linq".

And please stop top-posting.

Nevertheless, I need an example for this attitude (something that works with
database), please.

Read here: http://msdn.microsoft.com/de-de/library/bb399567.aspx

 

[Mr. X.]

Thank you.
You gave a link in Germen.
I think the English version is on :
http://msdn.microsoft.com/en-us/library/bb399567(en-us).aspx

Thanks
I will check that out.

As I understood from this thread, there isn't any feature, that translate
the object.where to a real where-statement in sql.
(Even behind code).

You seem to ignore my text. The entity framework (for example) does
exactly that. It's just NOT a feature of "linq".

And please stop top-posting.

Nevertheless, I need an example for this attitude (something that works
with
database), please.

Read here: http://msdn.microsoft.com/de-de/library/bb399567.aspx

--
Felix Palmen (Zirias) + [PGP] Felix Palmen <[email protected]>
web: http://palmen-it.de/ | http://palmen-it.de/pub.txt
my open source projects: | Fingerprint: ED9B 62D0 BE39 32F9 2488
http://palmen-it.de/?pg=pro + 5D0C 8177 9D80 5ECF F683