Linq no-noes - the catch all sql-like select?

Question

Hi,

One of the things that you have beaten into you as a junior developer is that you never, ever do a "SELECT *" on a data set, as it is unreliable for several reasons.

Since moving over to Linq (firstly Linq to SQL and then the Entity Framework), I have wondered if the Linq equivalent is equally frowned upon?

Eg

var MyResult = from t in DataContext.MyEntity
               where t.Country == 7
               select t;

Should we be selecting into an anonymous type with just the fields we want explicitly mentioned, or is the catch all select now acceptable for LinqToSql et al because of the extra stuff surrounding the data they provide?

Regards

Moo

Answer 1

select t in this case s selecting all fields from a known type. It is strongly typed and less subject to the same errors found in SQL.

For example in SQL

INSERT INTO aTable
SELECT * FROM AnotehrTable

could fail if AnotherTable changed, however in Linq / .Net this situation doesn't appear.

If you are joining multiple tables, then you can't do a select * in Linq, you would have to create an anonymous type with all types contained within.

Answer 2

I would say what you are doing is the equivalent to a SELECT * statement. It is better to return only the fields you require e.g.

var myResult = from t in DataContext.MyEntity
               where t.Country == 7
               select new T
               {
                   Field1 = t.Field1,
                   Field2 = t.Field2
               }

Answer 3

It's not frowned upon, it's determined by your use case. If you want to update the result and persist it then you should select t, however if you don't want to do that and are just querying for display purposes you can make it more efficient by selecting the properties you want:

var MyResult = from t in DataContext.MyEntity
               where t.Country == 7
               select new { t.Prop1, t.Prop2 };

This is for a few reasons. The population of an anonymous type is slightly faster, but more importantly it disables change tracking...because you can't persist an anonymous type, there's no need to track changes to it.

Here's an excellent rundown of the common performance areas like this that's great when starting out. It includes a more in-depth explanation of the change tracking I just described as well.

Answer 4

You should still explicitly state what you want to select. If you select all, you are still pulling down far more data than you need and as new things are added, you will unnecessarily pull those as well. In general, a best practice is to pull only what you need.

Answer 5

Using LINQ will not alleviate the performance hit of getting extra fields.

However, it is impossible to generate a SELECT * FROM ... using LINQ to SQL. Your code will generate a SELECT statement that explicitly names all of the columns defined in your model; it will ignore any changes to the database.

However, performance is still a concern, so you should use an anonymous type if you're only using some of the columns.

Answer 6

It may be necessary to do it as in the example, especially if what needs to be done is a change to the row(s).

in SQL select * is different than linq because linq will always return the same number of columns (as defined in the dbml).

Answer 7

The reason for avoiding SELECT * is that the underlying database might change and therefore column orders might change which could result in bugs in your data access layer.

You are not performing a SELECT * from your database, you are just saying that you want "t" and everything that goes with it. There is nothing wrong with that if that is truly what you need.