Nulls - LINQ to DataSets Part 3

re: Nulls - LINQ to DataSets Part 3

onovotny — Tue, 13 Feb 2007 20:43:49 GMT

What kind of extensibility will there be in the LINQ to SQL implementation?

So far, nothing I've seen shows that you can use CTE's or Full-Text functions directly from LINQ code. It would be very useful if there was a mechanism where additional functions could be added to expression tree that could add additional SQL calls.

Is there any way to do this?

re: Nulls - LINQ to DataSets Part 3

Frans Bouma — Tue, 13 Feb 2007 22:17:47 GMT

I asked a question yesterday about your part 2 of the entity data model. You didn't reply. Could you please answer my question? thanks :)

re: Nulls - LINQ to DataSets Part 3

Beth Massi — Tue, 13 Feb 2007 22:23:57 GMT

I think your VB syntax is incorrect. Shouldn't it be:

Dim query = From dataRow In orderDataTable _

Where IIF(dataRow.IsNull("OrderDate"), False, dataRow.Field(Of DateTime)("OrderDate").Year = 2006) _

Select dataRow.Field(Of Integer)("OrderID")

re: Nulls - LINQ to DataSets Part 3

Erick Thompson — Wed, 14 Feb 2007 02:25:50 GMT

Beth,

Thanks for the catch! The code should be updated soon.

Frans,

The author of that post will respond to your comment on that post, but if you have any LINQ to DataSet related questions, please post!

re: Nulls - LINQ to DataSets Part 3

Frans Bouma — Wed, 14 Feb 2007 18:36:04 GMT

I don't have any dataset related questions, or perhaps it's related, but what I really want the ADO.NET vNext team to comment on are 2 things:

1) the 'where is what executed' problem. See:

http://www.thedatafarm.com/blog/2007/02/13/TheShadowySideOfLINQToSQL.aspx

It contains a Linq query which actually doesn't result in a single query but in a query which results are evaluated in-memory, which isn't expected at all, and thus will result in a lot of queries. (point 2 in that blog). When is what executed in the db and when is what executed in-memory, which could trigger lazy loading or other slow constructs. To me, if Linq doesn't have a solution for this, it's dead in the water as too many people will be bitten by it sooner or later. It should be deterministic where what is executed (e.g. in-memory with a function or in the db with a query predicate/fragment)

2) the explicit specification of a subquery instead of a join. Take northwind, and the employee - order - customer relations. Now, there are 8 or 9 employees in the db, a few hundred orders and 89 or so customers. If I want all employees who have accepted an order from a customer in Germany, I can do that with JOINs or with subqueries. The fact is, with JOINs, I get a lot of duplicates for the employee data (due to the 1:n relation), and I can't use DISTINCT for the select, as Employee contains a blob field. It therefore will be very slow as the data has to be filtered on the client (o/r mapper core) and with subqueries this isn't the case, the code is very fast. The thing is though: how to specify in Linq that a subquery has to be used instead? For northwind, it might be still acceptable, but if I have 500,000 orders and 150,000 customers, I won't be very happy with the join approach.

Again, if Linq can't express this, it won't be that useful for a lot of applications. It would be great to see how to address these two points in Linq code.

Thanks.

re: Nulls - LINQ to DataSets Part 3

RKJ — Wed, 14 Feb 2007 19:04:16 GMT

Could anyone please provide an example of using ExceptRows function in a sql query ?

ExceptRows

Public Function ExceptRows( _

first as IEnumerable(Of DataRow), _

second as IEnumerable(Of DataRow)) _

as IEnumerable(of DataRow)

For example please use the following query: Replace operator "<>" with Except Rows and "=" with EqualRows.

Dim RetTable As DataTable = (From o In (From A In Elite _

Select A), P In Person _

Where LTrim(RTrim(o.Field(Of String)("TimeKeeperNumber"))) = LTrim(RTrim(P.Field(Of String)("TimeKeeperNumber"))) _

And UCase(LTrim(RTrim(o.Field(Of String)("Email_Address")))) <> UCase(LTrim(RTrim(P.Field(Of String)("Email_Address")))) _

Select TimeKeeperNumber := o.Field(Of String)("TimeKeeperNumber"), _

Last_Name := P.Field(Of String)("Person_Last_Name")).ToDataTable()

re: Nulls - LINQ to DataSets Part 3

Beth Massi — Wed, 14 Feb 2007 23:37:03 GMT

Frans,

Re: 2) What I want to know is if that same issue comes up when querying the Entity Data Model. I see using LINQ to the EDM more than directly like LINQ to SQL.

And I'd like to hear what kind of guidance is Microsoft going to give regarding using LINQ to SQL directly vs. the Entity Framework? With LINQ to EDM I can't yet see where LINQ to SQL should be used except for really small, trivial applications.

Querying DataSets – Introduction to LINQ to DataSet

ADO.NET team blog — Thu, 15 Feb 2007 23:51:34 GMT

Greetings fellow data junkies! My name is Erick Thompson, a PM at Microsoft who is working on driving

Type safety - LINQ to DataSets Part 2

ADO.NET team blog — Thu, 15 Feb 2007 23:53:00 GMT

Back in the first post of this LINQ to DataSet series, I spent some time talking about what LINQ to DataSet

re: Nulls - LINQ to DataSets Part 3

DiegoV — Sat, 17 Feb 2007 20:10:07 GMT

Hello Erick, It is good to have something to read while the February CTP (or is it a March CTP?) gets out!

I happen to use Visual Basic a lot, and just wanted to comment:

1. The short-circuited behavior of IIF is new to Orcas, but the operator has been part of the language for many years (only it was just like a normal function call that evaluated all parameters). For what I know this is still subject to change in the final version (http://www.panopticoncentral.net/archive/2006/12/29.aspx)

2. I think the use of ANDALSO and IIF is a matter of taste, but I personally like more ANDALSO in this case :)

3. I prefer not to compare a Boolean function like IsNull() to true or false as you do in your code, but again, it is matter of taste. Perhaps my problem is that I have seen really ugly code do things like that :)

Now, for a couple of questions:

1. With Field<T>() returning nullable types you mean that it is also safe to use something like Field<T>(name).HasValue if T is a value type?

2. Is it going to be possible to change the settings of the Typed DataSet generator to get fields with nullable types in Orcas?

Thanks for your post.

New and Notable 142

Sam Gentile — Sun, 18 Feb 2007 19:16:41 GMT

Data/ADO.NET Orcas Two from the ADO.NET team: Entity Client and Nulls - LINQ to DataSets Part 3 Software

re: Nulls - LINQ to DataSets Part 3

Erick Thompson — Thu, 01 Mar 2007 01:38:57 GMT

Diego,

Thanks for the great comments! FTYI, The Feb/March CTP is now out, so take a look!

ANDALSO is definitely an option, and I can see why people like that more. However, I've found that a lot of people aren't familiar with ANDALSO, and they get stuck on the short circuiting part of it.

For your questions, if you use Field<T> you can definitely use HasValue to check for a null value, which is yet another way you can write these queries.

As for the Typed DataSet returning nullable types, that is something we're looking at doing for RTM.

Thanks!

Erick

re: Nulls - LINQ to DataSets Part 3

Bill McCarthy — Mon, 21 May 2007 05:07:01 GMT

The syntax around IIf is completely wrong. IIf is a function with the syntax of :

Function IIf(expression As Booleam, _

returnTrue As Object, _

returnFalse As Object)

As Object

So when you pass to IIf a statment such as :

Dim query = From dataRow In orderDataTable _

Where IIF(dataRow.IsNull("OrderDate"), False, dataRow.Field(Of DateTime)("OrderDate").Year = 2006) _

Select dataRow.Field(Of Integer)("OrderID")

it means that the parameters otthe function all get evaluated, including :

dataRow.Field(Of DateTime)("OrderDate").Year = 2006

even when dataRow.IsNull("OrderDate") is true.

This is a language limitation that will be addressed in Orcas with the new If tenrary operator. The correct code will be:

Dim query = From dataRow In orderDataTable _

Where If(dataRow.IsNull("OrderDate"), False, dataRow.Field(Of DateTime)("OrderDate").Year = 2006) _

Select dataRow.Field(Of Integer)("OrderID")

re: Nulls - LINQ to DataSets Part 3

Patrick — Wed, 14 Nov 2007 00:44:06 GMT

Hi,

I have a typed Dataset with two related data tables that I am joining together on a single relation. I want to then put the result [of the select] back into a third data table. This seems to be quite difficult! Can you suggest a method for achieving this?

var joined = from p in dset.Donations.AsEnumerable()

join e in dset.Name_and_Address on p.Member_ID equals e.Member_ID

select new { Donation_ID = p.Field<int>("Donation ID"), Member_ID = p.Field<int>("Member ID"),

Date = p.Field<DateTime>("Date"), Amount = p.Field<double>("Amount"),

Title = e.Field<string>("Title"), Initials = e.Field<string>("Initials"),

Surname = e.Field<string>("Surname") };

The source data may contain DBNull.

re: Nulls - LINQ to DataSets Part 3

Travis Riffle — Wed, 05 Mar 2008 01:37:06 GMT

I just wanted to say this is exactly what I was looking for... not testing IsNull was what was causing my exception problems in my datatable.

Good info!

re: Nulls - LINQ to DataSets Part 3

pramod.gupta — Tue, 22 Jul 2008 11:21:32 GMT

How do i write a dynamic Linq query with Group Join over Two DataTables.

Constraint columns can be more than one.

re: Nulls - LINQ to DataSets Part 3

Gabriel Bogea Perez — Fri, 08 Aug 2008 22:15:54 GMT

Hey, what if I want to use a column from a Typed Dataset in an OrderBy and this column may have nulls? How to deal with this?

var query = from row in dataTable.AsEnumerable()

where row.IdCompany == 5

orderby row.Age

I get an error saying that the column Age is DBNull, which is expected since the Property in the DataTable throws an exception when you try to cast the null value.

In this case Age may be null. I have also tried this:

var query = from row in dataTable.AsEnumerable()

where row.IdCompany == 5

orderby row["Age"]

but it fails in the CompareTo method.

Any ideas?

re: Nulls - LINQ to DataSets Part 3

shankar — Thu, 25 Sep 2008 09:39:00 GMT

Hi ,

I just want to build the where clause of the query dynamicaly. Kindly help me out