Extract Data from 3 linq tables..

how to extract data from several tables using linq..

Page 1 of 1

9 Replies - 5541 Views - Last Post: 09 February 2009 - 05:31 PM Rate Topic: -----

#1 martinch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 08

Extract Data from 3 linq tables..

Posted 04 February 2009 - 03:53 AM

hi C# people,

I want to get the searched persons from my DB. You can see my DB/linqDatacontext with in my picture.

Attached Image

public static List<Person> GetPersonsBySearch(string fromDate, string toDate, string registerDate, int competenceId, string Name)
		{

 var query = from pers in db.persons
				   join avail in db.availabilities on pers.person_id equals avail.person_id into ap
				   join comp in db.competence_profiles on pers.person_id equals comp.person_id
				   from o in ap 
				  where (avail.from_date >= FromDate && avail.to_date <= ToDate) || (pers.name == Name) || (pers.registerdate == RegisterDate) || (comp.competence_id ==(long)competenceId)
					select o;



So its a mess.. and i can't get into the msdn site.. :crazy:

If you know how the query should look like, please let me know.. :^:

I will check this forum later today. :ph34r:

Thanks, -M-Sweden-

Is This A Good Question/Topic? 0
  • +

Replies To: Extract Data from 3 linq tables..

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,958
  • Joined: 21-March 08

Re: Extract Data from 3 linq tables..

Posted 04 February 2009 - 09:25 AM

can you explain what data you are actually looking for? maybe there is a different way to do the query.

This post has been edited by eclipsed4utoo: 04 February 2009 - 09:25 AM

Was This Post Helpful? 0
  • +
  • -

#3 martinch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 08

Re: Extract Data from 3 linq tables..

Posted 06 February 2009 - 03:08 AM

Well, the data I'm looking for is whatever the user sends thru the parameters in the GetPersonsBySearch method.

It could be username, or a certain competence, or both a user name and a competence matching a person in the DB.

Did that explain it better?

Thanx. Martin
Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,958
  • Joined: 21-March 08

Re: Extract Data from 3 linq tables..

Posted 06 February 2009 - 06:30 AM

my advice would be to split it up into different queries depending on the data the user enters.

I wouldn't even know how to do that in SQL. Once you do the join, you are going to use that table whether the user needs to or not. If the user doesn't put in a "competence_id", the query is still going to use the "competence_profiles" table and you are going to return loads of duplicate records.

This post has been edited by eclipsed4utoo: 06 February 2009 - 06:30 AM

Was This Post Helpful? 0
  • +
  • -

#5 martinch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 08

Re: Extract Data from 3 linq tables..

Posted 09 February 2009 - 01:47 AM

Yes im gettin duplicate records now.. im goning crazy over here..

Spitting up.. i was starting to do that but I will be sooo many IF statements it will look soo ugly...

Anyways.. I got one thing to work, but only from one table :-/

check this blog out, it works, but only from one table..

http://www.rocksthou...ic-queries.aspx

anyone?? ...ooooh...

martin
Was This Post Helpful? 0
  • +
  • -

#6 TEH  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 29
  • View blog
  • Posts: 152
  • Joined: 30-January 08

Re: Extract Data from 3 linq tables..

Posted 09 February 2009 - 01:57 AM

I dont know Linq, but in SQL the Left Join-command will limit duplicates off.
Was This Post Helpful? 0
  • +
  • -

#7 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,958
  • Joined: 21-March 08

Re: Extract Data from 3 linq tables..

Posted 09 February 2009 - 06:38 AM

View PostTEH, on 9 Feb, 2009 - 03:57 AM, said:

I dont know Linq, but in SQL the Left Join-command will limit duplicates off.


technically, yes it would. However, without giving it an ID to limit the results from the table, it's going to return the entire table.
Was This Post Helpful? 0
  • +
  • -

#8 martinch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 08

Re: Extract Data from 3 linq tables..

Posted 09 February 2009 - 06:48 AM

I have been trying now for..like 20 h..ooow.. im so tired...

i really need to make this work...

if i split up the query in 3 querys then i will end up with 3 persons list.

then i would have to compare them all 3 with each other.

there have to be a way..........

common experts........
Was This Post Helpful? 0
  • +
  • -

#9 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,958
  • Joined: 21-March 08

Re: Extract Data from 3 linq tables..

Posted 09 February 2009 - 07:33 AM

sadly, not many people have moved to using LINQ-To-SQL. I've posted numerous questions about LINQ on multiple forums and have never been able to get an answer
Was This Post Helpful? 0
  • +
  • -

#10 martinch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 08

Re: Extract Data from 3 linq tables..

Posted 09 February 2009 - 05:31 PM

I know.. Ive read several post like mine, but no answers..

Its like I want to mail microsoft and ask ;-)

I managed to get a joint work, but the problem now is..

1) I don't know in advance what parameters won't be null.. so I cant just have: where (p.name == name etc etc..

Ive tried with building up a querystring.. but that dident work since it will be a hell to cast it back to boolean expression :-)

Im of to bed. 15h of testing and trying, and noo progress.. oh linq, i love u...

//martin
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1