dharric08
asked on
Doing Complex Multi Joins in Linq
I'm trying to get this linq query to work, but its not returning any data. Unfortunately I do need to join all those tables to get my desired data. A similar query using Sql does work with the same set of joins (of course syntax is different though).
from c in hs.Computer
join tcp in hs.TCP on c.Computer_Idn equals tcp.Computer_Idn
join cs in hs.CompSystem on c.Computer_Idn equals cs.Computer_Idn
join ns in hs.NetworkSoftware on c.Computer_Idn equals ns.Computer_Idn
join p in hs.Processor on c.Computer_Idn equals p.Computer_Idn
join mem in hs.Memory on c.Computer_Idn equals mem.Computer_Idn
join ms in hs.MemorySlot on c.Computer_Idn equals ms.Computer_Idn
where c.DeviceName.Contains(wkstn)
select new Hardware
{
DeviceName = c.DeviceName,
Type = c.Type,
LoginName = c.LoginName,
FullName = c.FullName,
HWLastScanDate = (c.HWLastScanDate == null ? DateTime.MinValue : (DateTime)c.HWLastScanDate),
DomainName = c.DomainName,
ComputerLocation = c.ComputerLocation,
LDAPLocation = c.LDAPLocation,
SerialNum = cs.SerialNum,
Model = cs.Model,
Manufacturer = cs.Manufacturer,
Cpu = p.Type,
Speed = (p.Speed == null ? 0 : (int)p.Speed),
TotalMemoryMB = (mem.BytesTotal == null ? 0 : (int)mem.BytesTotal) / 1024,
MemorySlotNo = ms.SlotNo,
SizeInstalledMemory = (ms.InstalledSize == null ? 0 : (int)ms.InstalledSize),
ChassisType = cs.ChassisType,
IsLaptop = cs.HasBattery,
NicAddress = ns.NicAddress
}
ASKER
Well here's a few points. I'm using the entity framework and creating a new Hardware object. And Vs does not give any compile or runtime errors whatsoever. It just doesn't return anything.
sorry blank comment: Yes all looks good for me.
nope.. all looks good for me.
Ok seems problems with DateTime.MinValue - 'DateTime.MinValue underflow in SQL Server'
for test: change DateTime.MinValue to some fixed date and give a try.
for test: change DateTime.MinValue to some fixed date and give a try.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sree_ven, you're good! Now that you said it it seems so obvious. But what I don't get is there's no errors on compile or runtime. Shouldn't I have gotten a SqlDateTime overflow or something? Do you know why that is?
For your info:
The reason for the overflow is because .NET framework can cover a wider range of date than SQL Server. The DateTime structure in .NET supports a range from 0:00 1/1/0001 (MinValue) to 23:59 12/31/9999 (MaxValue). Whereas, SQL Server can only support a range of 1/1/1753 to 12/31/9999. Other databases handle datetime range differently.
The reason for the overflow is because .NET framework can cover a wider range of date than SQL Server. The DateTime structure in .NET supports a range from 0:00 1/1/0001 (MinValue) to 23:59 12/31/9999 (MaxValue). Whereas, SQL Server can only support a range of 1/1/1753 to 12/31/9999. Other databases handle datetime range differently.
ASKER
That's cool, but what I don't get is why I got no errors of any kind either during compile or at runtime. Could this be because of the Entity Framework layer hiding it?
it raises SqlException : System.Data.SqlTypes.SqlTy peExceptio n: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
Try putting your code in Try catch and catching SqlExceptions.
Try putting your code in Try catch and catching SqlExceptions.
....
where c.DeviceName.Contains(wkst
select new
{
DeviceName = c.DeviceName,....