SQL to LINQ questions

[Dan Tallent]

I have a query which I would like to convert to LINQ. I am having
problems finding good examples or references that cover more complex
queries.
Here is the SQL command I would like to rewrite into C# LINQ. Notice
that one of the returned fields "XrefID" is coming from the second table.
This information is very important but its complicating the query expression
in C# The DataContext has the two classes for the two tables (Customer
and Xref). This particular query needs information from both tables.

Does anyone have any good references that can help me writing this type of
query using LINQ?
Or does anyone know how I would go about dealing with the scenerio I have
discribed here?


select Customer.*,
(select top 1 Xref.XrefID from Xref where Customer.CustomerID =
Xref.CustomerID and Customer.CompanyID = Xref.CompanyID) as XrefID

from Customer

where Customer.CustomerID = 70540 and Customer.CompanyID = 6

 

[Pavel Minaev]

I have a query which I would like to convert to LINQ. I am having
problems finding good examples or references that cover more complex
queries.
Here is the SQL command I would like to rewrite into C# LINQ.
Notice that one of the returned fields "XrefID" is coming from the second
table. This information is very important but its complicating the query
expression in C# The DataContext has the two classes for the two
tables (Customer and Xref). This particular query needs information from
both tables.

Does anyone have any good references that can help me writing this type of
query using LINQ?
Or does anyone know how I would go about dealing with the scenerio I have
discribed here?

You should use an anonymous type to aggregate data from records from both
tables:

http://msdn.microsoft.com/en-us/library/bb397696.aspx

Otherwise, the translation of your query should be pretty straightforward.
Of course, you'll need to explicitly list the fields rather than using "*".

 

[Dan Tallent]

You should use an anonymous type to aggregate data from records from both
tables:

http://msdn.microsoft.com/en-us/library/bb397696.aspx

Otherwise, the translation of your query should be pretty straightforward.
Of course, you'll need to explicitly list the fields rather than using
"*".

I plan on using anonymous types like the example you provided, but even in
the example you provide it does not show a join.

The code I was playing with looks like this:
//------------------------------------------------------------------------

var QueryCustName =

from cust in DC.Customers

join xref in DC.Xrefs

on cust.CustomerID equals xref.CustomerID

where cust.CompanyID = xref.CompanyID

where cust.CompanyID = 6

select new { cust.DisplayName, cust.Address1, xref.XrefID } ;

//------------------------------------------------------------------------

The problem is I would prefer to use the following syntax, but I am unsure
of how to do a join using this method. All of the examples I have found
look like the one above.

var QueryCustName =

from Cust in DC.Customers

..OrderBy(Cust => Cust.DisplayName)

..Where(Cust => Cust.CompanyID == mCompanyID)

..Take(50)

select new {Cust.DisplayName, Cust.Address1, Xref.XrefID };

//------------------------------------------------------------------------

any help would be appreciated.

Thanks

Dan

 

[Pavel Minaev]

I plan on using anonymous types like the example you provided, but even in
the example you provide it does not show a join.

The code I was playing with looks like this:
//------------------------------------------------------------------------

var QueryCustName =

from cust in DC.Customers

join xref in DC.Xrefs

on cust.CustomerID equals xref.CustomerID

where cust.CompanyID = xref.CompanyID

where cust.CompanyID = 6

select new { cust.DisplayName, cust.Address1, xref.XrefID } ;

If you are doing an equijoin on a complex key, it's better to do both
comparisons in the join - note that you can use anonymous types for
complex keys in such scenarios, i.e.:

join xref in DC.XRefs on new { cust.CustomerID, cust.CompanyID }
equals new { xref.CustomerID, xref.CompanyID }

//------------------------------------------------------------------------

The problem is I would prefer to use the following syntax, but I am unsure
of how to do a join using this method.  All of the examples I have found
look like the one above.

var QueryCustName =

from Cust in DC.Customers

.OrderBy(Cust => Cust.DisplayName)

.Where(Cust => Cust.CompanyID == mCompanyID)

.Take(50)

select new {Cust.DisplayName, Cust.Address1,  Xref.XrefID };

//------------------------------------------------------------------------

any help would be appreciated.

The obvious question is - what's wrong with the first code snippet?
Joins (both implicit and explicit) are one case where LINQ syntactic
sugar is usually much clearer than chained method calls.

However, if you insist on doing it the other way, then you just
translate LINQ join to Enumerable.Join:

DC.Customers
..Join(
DC.Xrefs,
cust => new { cust.CustomerID, cust.CompanyID },
xref => new { xref.CustomerID, xref.CompanyID },
(cust, xref) => new { cust.DisplayName, cust.Address1,
xref.XRefID })
..OrderBy(r => r.DisplayName)
..Take(50)

 

[Dan Tallent]

This looks really great! I'll start playing with it here in a minute.
I am still trying to understand how the code works, mainly because I could
not figure out how to use the .Take() function without using dot notation.

One more question... if I anonymous types, how would a DataGridView know
what the primary key is ? (for updating, deleting, etc)

Thanks
Dan

I plan on using anonymous types like the example you provided, but even in
the example you provide it does not show a join.

The code I was playing with looks like this:
//------------------------------------------------------------------------

var QueryCustName =

from cust in DC.Customers

join xref in DC.Xrefs

on cust.CustomerID equals xref.CustomerID

where cust.CompanyID = xref.CompanyID

where cust.CompanyID = 6

select new { cust.DisplayName, cust.Address1, xref.XrefID } ;

If you are doing an equijoin on a complex key, it's better to do both
comparisons in the join - note that you can use anonymous types for
complex keys in such scenarios, i.e.:

join xref in DC.XRefs on new { cust.CustomerID, cust.CompanyID }
equals new { xref.CustomerID, xref.CompanyID }

//------------------------------------------------------------------------

The problem is I would prefer to use the following syntax, but I am unsure
of how to do a join using this method. All of the examples I have found
look like the one above.

var QueryCustName =

from Cust in DC.Customers

.OrderBy(Cust => Cust.DisplayName)

.Where(Cust => Cust.CompanyID == mCompanyID)

.Take(50)

select new {Cust.DisplayName, Cust.Address1, Xref.XrefID };

//------------------------------------------------------------------------

any help would be appreciated.

The obvious question is - what's wrong with the first code snippet?
Joins (both implicit and explicit) are one case where LINQ syntactic
sugar is usually much clearer than chained method calls.

However, if you insist on doing it the other way, then you just
translate LINQ join to Enumerable.Join:

DC.Customers
..Join(
DC.Xrefs,
cust => new { cust.CustomerID, cust.CompanyID },
xref => new { xref.CustomerID, xref.CompanyID },
(cust, xref) => new { cust.DisplayName, cust.Address1,
xref.XRefID })
..OrderBy(r => r.DisplayName)
..Take(50)

 

[Dan Tallent]

Anonymous types....

If the LINQ query is returning an anonymous type, how can you pass this
object outside of your function / class ?

In my prior example I have a DC.Customers object class. When I pass this
to another function it is of type Customer.
This lets me write code in a function like:

//----------------------------------------------------------------------------------------------------------------
Object SelectedRecord = dataGridView1.SelectedRows[0].DataBoundItem;

RecordID = STC.GetRecordID(SelectedRecord);

//----------------------------------------------------------------------------------------------------------------

The class STC is actually a derived class. I have several different STC
type classes which work with different data (Customers, Inventory, Invoices,
etc)
They all have a function called GetRecordID which is expecting a Object

public override int GetRecordID(object SelectedRecord)

{

int RecordID = 0;


if (SelectedRecord.GetType() == typeof(Search.Customer))

{

Customer SelectedCust = (Customer)SelectedRecord;

RecordID = SelectedCust.CustomerID;

}


return RecordID;

//----------------------------------------------------------------------------------------------------------------

What would be the correct method of achieving this if I'm using anonymous
types?

Thanks again
Dan

 

[Pavel Minaev]

This looks really great!  I'll start playing with it here in a minute.
I am still trying to understand how the code works,  mainly because I  could
not figure out how to use the .Take() function without using dot notation..

You can't. However, since it usually comes last, you can enclose the
entire LINQ query into parentheses, and then apply Take() to it.

One more question... if I anonymous types, how would a DataGridView know
what the primary key is ?   (for updating, deleting, etc)

DataGridView doesn't deal with primary keys. When bound to an
updatable data source, it just treats it as a collection, and, if
possible, as an IBindingList.

 

[Pavel Minaev]

Anonymous types....

If the LINQ query is returning an anonymous type, how can you pass this
object outside of your function / class ?

You can't. You'll have to declare a named class to hold your values if
you want to have a function with such a return type.

 

[Marc Gravell]

You can't. You'll have to declare a named class to hold your values if

you want to have a function with such a return type.

Although an anonymous type can't be on the method signature, that
isn't /entirely/ true, in 2 different ways. The most common way of
passing an anonymous type out is via generics - but of course the
method you call won't know a lot about the anonymous type (other than
"look, there are some Ts..."). But also, you can still pass data
around as object, or List<object> (or IList) etc. This might not sound
very useful, but it is: it means you can use an anonymous type as a
DataSource for data-bound UI (such as a DataGridView etc).

Marc

 

[Dan Tallent]

I was looking into reflection to see how I could get one of the values from
the object. The object
was created by using the LINQ query as an anonymous type. I am just trying
to recover the value
of one of the fields within the object. I have been successful getting a
list of fields, but not getting the actual values.

I would not have a problem creating a class so I could simply cast this
object out, but I don't know how to create a LINQ query to use a user
defined class.
Keep in mind the result of my query is pulling data from two separate tables
so I can't simply use one of the classes in the datacontext.
public override int GetRecordID(object SelectedRecord)

{

int RecordID = 0;

Type objectType = SelectedRecord.GetType();

foreach (System.Reflection.MemberInfo MI in objectType.GetMembers())

{

Console.WriteLine(MI.Name);

}

}

Any help is greatly appreciated

Thanks

Dan

 

[Marc Gravell]

PropertyInfo and GetValue:

using System;

class Program
{
static void Main()
{
object obj = GetSomeObject();
foreach (var prop in obj.GetType().GetProperties())
{
Console.WriteLine("{0}={1}", prop.Name, prop.GetValue(obj,
null));
}
}
static object GetSomeObject()
{
return new { Foo = 1, Bar = "abc" };
}
}

 

[Marc Gravell]

but I don't know how to create a LINQ query to use a user

defined class

The same as you do to an anon-type. LINQ doesn't even know about
anonymous types:

public class Test {
public string Foo {get;set;}
public int Bar {get;set;}
}
....
select new Test {Foo = x.SomeProp, Bar = y.SomeOtherProp};

The only caveat is that the data-context won't usually let you create
instances of objects that *it* thinks it controls - i.e. if you have a
SomeDataRecord type in your LINQ context, you can't use "select" to
create a new SomeDataRecord manually - you need to just let the data-
context create it.

Marc

 

[Marc Gravell]

LINQ doesn't even know about anonymous types:

By which I mean that Expression / LINQ-to-SQL etc don't have any
specific knowledge of them - just that an object of some Type should
be created. Obviously anonymous types are a key part of the overall
LINQ design at the caller...

Marc

 

[Dan Tallent]

Great example, thanks. I was able to use this to get the information I
needed.

Thanks again
Dan