Great! Thanks every one for their suggestions
This works well
void Main()
{
string con = @"Data Source=DEVELOPMENT2\SQLSERVER2008;Initial
Catalog=PathWestStaff;Integrated Security=True";
DataContext db = new DataContext(con);
var permissions = db.GetTable<Admin>();
var stafflist = db.GetTable<Staff>();
var query =
from authorization in permissions
join staffmember in stafflist
on authorization.StaffId equals staffmember.StaffId
where staffmember.HE_Number.Contains("he1234567")
select authorization;
var individual = query.FirstOrDefault();
if(individual == null) individual = new Admin();
individual.Dump();
}
Tried Michael's suggestion
void Main()
{
string con = @"Data Source=DEVELOPMENT2\SQLSERVER2008;Initial
Catalog=PathWestStaff;Integrated Security=True";
DataContext db = new DataContext(con);
var permissions = db.GetTable<Admin>();
var stafflist = db.GetTable<Staff>();
var query =
from staffmember in stafflist
from authorization in staffmember.Admins
where staffmember.HE_Number.Contains("he1234567")
select authorization;
var individual = query.FirstOrDefault();
if(individual == null) individual = new Admin();
individual.Dump();
}
Works great as well.
Tried this next
void Main()
{
string con = @"Data Source=DEVELOPMENT2\SQLSERVER2008;Initial
Catalog=PathWestStaff;Integrated Security=True";
DataContext db = new DataContext(con);
//var permissions = db.GetTable<Admin>();
//var stafflist = db.GetTable<Staff>();
var query =
from staffmember in db.GetTable<Staff>()
from authorization in staffmember.Admins
where staffmember.HE_Number.Contains("he1234567")
select authorization;
var individual = query.FirstOrDefault();
if(individual == null) individual = new Admin();
individual.Dump();
}
Looks like there are many ways to skin the same cat (nothing unusual there)
I can see why everyone loves this stuff.
Regards Peter Maddin
Applications Development Officer
PathWest Laboratory Medicine WA
Phone : +618 6396 4285 (Monday, Wednesday,Friday)
Phone : +618 9346 4372 (Tuesday, Thursday)
Mobile: 0423 540 825
E-Mail : [email protected]; [email protected]
The contents of this e-mail transmission outside of the WAGHS network are
intended solely for the named recipient's), may be confidential, and may be
privileged or otherwise protected from disclosure in the public interest.
The use, reproduction, disclosure or distribution of the contents of this
e-mail transmission by any person other than the named recipient(s) is
prohibited. If you are not a named recipient please notify the sender
immediately.
From: [email protected] [mailto:[email protected]]
On Behalf Of Michael Minutillo
Sent: Wednesday, 28 March 2012 1:31 PM
To: ozDotNet
Subject: Re: Very simple LINQ query
If there are foreign key constraints in the database then you should be able
to treat the rows as objects (from a linq perspective) and dot into them. In
other words, LINQ can figure out the join details for you:
from s in stafflist
from p in s.permissions
where s.HE_Number.Contains("whatever")
select p
You'd need to have a look at the query generated.
The result you get back should already be of type IQueryable<Admin> so you
can just drop a .ToList() or .ToArray() on the end to get a more concrete
object that you can store in the session. If there is no data then the
array/list will be empty (but still serializable). If you're expecting to
get a single result back then you can use .FirstOrDefault() or
.SingleOrDefault() which will get you either a single Admin record or null
(both of which should be serializable).
Michael M. Minutillo
Indiscriminate Information Sponge
http://codermike.com
On Wed, Mar 28, 2012 at 11:53 AM, James Chapman-Smith
<[email protected]> wrote:
Hi Peter,
Based on what you've asked it sounds like you just need to do this:
query.DefaultIfEmpty().Dump();
It sounds like that's what you were thinking already. Unless I've missed
what you're asking for.
Cheers.
James Chapman-Smith
Description: qrcode - james@cs
From: [email protected] [mailto:[email protected]]
On Behalf Of Peter Maddin
Sent: Wednesday, 28 March 2012 13:57
To: [email protected]
Subject: Very simple LINQ query
I need to query a database and get a single record back or an empty record.
I know this is not too hard, but I also need to get back a named object and
not an anonymous one.
I want to persist this in session state for an asp.net application so I need
something that can be serialized.
Using LINQPad this is something what I am trying to achieve
void Main()
{
string con = @"Data Source=DEVELOPMENT2\SQLSERVER2008;Initial
Catalog=PathWestStaff;Integrated Security=True";
DataContext db = new DataContext(con);
var permissions = db.GetTable<Admin>();
var stafflist = db.GetTable<Staff>();
var query =
from authorization in permissions
join staffmember in stafflist
on authorization.StaffId equals staffmember.StaffId
where staffmember.HE_Number.Contains("he1234567")
select authorization;
query.Dump();
}
The above works but I need to get a single named instance I can store in
session state and would like to use the DefaultIfEmpty() extension when
there is no record.
I think I need to use an outer rather than an inner join.
I was hoping that a LINQ expert could fast track me here.
Regards Peter Maddin
Applications Development Officer
PathWest Laboratory Medicine WA
Phone : +618 6396 4285 <tel:%2B618%206396%204285> (Monday,
Wednesday,Friday)
Phone : +618 9346 4372 <tel:%2B618%209346%204372> (Tuesday, Thursday)
Mobile: 0423 540 825
E-Mail : [email protected]; [email protected]
The contents of this e-mail transmission outside of the WAGHS network are
intended solely for the named recipient's), may be confidential, and may be
privileged or otherwise protected from disclosure in the public interest.
The use, reproduction, disclosure or distribution of the contents of this
e-mail transmission by any person other than the named recipient(s) is
prohibited. If you are not a named recipient please notify the sender
immediately.
<<image001.png>>
