Solved

Doing Complex Multi Joins in Linq

Posted on 2009-04-08
11
217 Views
Last Modified: 2013-11-11
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

}

Open in new window

0
Comment
Question by:dharric08
  • 8
  • 3
11 Comments
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24102224
shouldn't it  be for 'where' as :
....
where c.DeviceName.Contains(wkstn)
select new
{
     DeviceName = c.DeviceName,....
0
 

Author Comment

by:dharric08
ID: 24102336
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.
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24102505
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24102516
sorry blank comment:   Yes all looks good for me.
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24102597
nope.. all looks good for me.  
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24102611
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.

0
 
LVL 9

Accepted Solution

by:
Sreedhar Vengala earned 500 total points
ID: 24102639
use SqlDateTime.MinValue instead DateTime.MinValue and give a try
0
 

Author Comment

by:dharric08
ID: 24102672
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?
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24102726
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.
0
 

Author Comment

by:dharric08
ID: 24102748
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?
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24102769
it raises SqlException : System.Data.SqlTypes.SqlTypeException: 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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now