How do I create multiple LINQ Queries

I am using LINQ queries for a Visual C# to Access DB

Page 1 of 1

7 Replies - 2319 Views - Last Post: 11 June 2010 - 08:04 AM Rate Topic: -----

#1 RMckelvie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 16-April 10

How do I create multiple LINQ Queries

Posted 10 June 2010 - 03:00 AM

Hiya there. I have been working with Linq queries where I am using C# to communicate with individual tables for my project. Each one bears primary keys and I have called them DVD and Member (Primary keys: memberNo and dvdNo).

The problem is that there is another table called Rental that uses the primary keys of DVD and Member (making them foreign keys) and I do not know how to link two primary keys from different tables to be used in one query

This post has been edited by RMckelvie: 10 June 2010 - 03:09 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How do I create multiple LINQ Queries

#2 JITHU  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 62
  • View blog
  • Posts: 201
  • Joined: 02-July 07

Re: How do I create multiple LINQ Queries

Posted 10 June 2010 - 03:50 AM

Why don't you just use LINQ to SQL?
Was This Post Helpful? 0
  • +
  • -

#3 RMckelvie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 16-April 10

Re: How do I create multiple LINQ Queries

Posted 10 June 2010 - 04:07 AM

View PostJITHU, on 10 June 2010 - 02:50 AM, said:

Why don't you just use LINQ to SQL?


I am not very good with SQL and I was told to write the project with Access. Some people in my college class have been having a lot of problems with SQL Server in the labs.

In short, Access is easier and I can't afford to change from Access to SQL as I am near a submission deadline.

In terms of coding, what is the difference?

This post has been edited by RMckelvie: 10 June 2010 - 04:10 AM

Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

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

Re: How do I create multiple LINQ Queries

Posted 10 June 2010 - 05:36 AM

I am confused. How are you using LINQ with a database but not using LINQ-To-SQL(or Entity Framework)? Can you post a query that you have written? Are you looking for a LINQ query or a SQL query?

Also, I am not sure why you gave JITHU negative rep. You stated in your original post that you were using LINQ, but it doesn't seem like you are using LINQ-to-SQL. The first question anybody would have asked was why you weren't using LINQ-to-SQL since you didn't initially state that you were using Access.

This post has been edited by eclipsed4utoo: 10 June 2010 - 05:38 AM

Was This Post Helpful? 2
  • +
  • -

#5 JITHU  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 62
  • View blog
  • Posts: 201
  • Joined: 02-July 07

Re: How do I create multiple LINQ Queries

Posted 10 June 2010 - 06:06 AM

Sorry if i misunderstood your first post.

In your first post, you haven't specified that you were using Access. So i think, it's normal to assume that one is using a decent DB system like MSSQL. I'm not saying that Access is bad.

If you're not proficient with SQL, then there is LINQ to SQL.

Quote

In terms of coding, what is the difference?

You write less code, and you don't have to use SQL.
In addition, it creates you all of your db tables as classes. It's easier to do update,insert,delete operations.
You can use LINQ to SQL also with Access, but it's not supported officially, so you've to manually implement it yourself. As you don't have time, it's not for you.

Note that SQL doesn't mean SQL Server. Also if you don't want to use LINQ, why the title is "How do I create multiple LINQ Queries"?

Now to your real problem:
In order to link rentals with members table or dvds table to gather some useful informations, you should use inner joins, here SQL is not a plus.

If your tables structure is something like this:
Rentals(memberNo, dvdNo, rentalDate) // memberNo and dvdNo are FKs
Members(memberNo, name) // memberNo is PK
DVDs(dvdNo, title) // dvdNo is PK

A sample query:
Get all rentals for each member and show MemberNo, Name, Title of DVD and the Rental Date

select memberNo, name, title, rentalDate
from Rentals inner join Members on Rentals.memberNo = Members.memberNo
	inner join DVDs on Rentals.dvdNo = DVDs.dvdNo
group by memberNo


BTW i haven't tested it with an Access db(i don't have Access installed on my PC).

Quote

Help us to help you.

This post has been edited by JITHU: 10 June 2010 - 07:23 AM

Was This Post Helpful? 3
  • +
  • -

#6 RMckelvie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 16-April 10

Re: How do I create multiple LINQ Queries

Posted 11 June 2010 - 01:15 AM

View Posteclipsed4utoo, on 10 June 2010 - 04:36 AM, said:

I am confused. How are you using LINQ with a database but not using LINQ-To-SQL(or Entity Framework)? Can you post a query that you have written? Are you looking for a LINQ query or a SQL query?

Also, I am not sure why you gave JITHU negative rep. You stated in your original post that you were using LINQ, but it doesn't seem like you are using LINQ-to-SQL. The first question anybody would have asked was why you weren't using LINQ-to-SQL since you didn't initially state that you were using Access.


From my understanding, I am linking an Access database to my project and I assume that there is a little bit of LINQ syntax embedded onto it.

There are some codewords like "OleDbCommand", "OleDbConnection", "OleDbDataAdapter" that I have used.

Just to give a better understanding on what I am using, I will submit the global variables and the method I use for obtaining data.



   //sets up a dictionary of members and dvds and conenction establishment
        private Dictionary<int, Member> members;
        private Dictionary<int, DVD> dvds;
        private OleDbConnection con;
        private OleDbDataAdapter myDA;

        private DataSet myDS;



public void obtainData()
  {
      OleDbCommand cmd1;
      OleDbCommand cmd2;

      con = new OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\\cVideos.mdb");
      con.Open();

      // commands the database to view all records in Member table
      cmd1 = new OleDbCommand("Select * FROM Member",con);
      cmd2 = new OleDbCommand("Select * FROM DVD", con);

      myDA = new OleDbDataAdapter(cmd1);
      myDS = new DataSet();
      myDA.Fill(myDS, "Member");
      myDA.SelectCommand = cmd2;
      myDA.Fill(myDS, "DVD");

      getMembers();
      getDVD();
      con.Close();
  }




PS: Please bear with the newbie :(

This post has been edited by RMckelvie: 11 June 2010 - 01:23 AM

Was This Post Helpful? 0
  • +
  • -

#7 JITHU  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 62
  • View blog
  • Posts: 201
  • Joined: 02-July 07

Re: How do I create multiple LINQ Queries

Posted 11 June 2010 - 04:43 AM

A suggestion, you must learn first SQL before attempting to create applications interacting with databases.

Some useful links:
SQL Joins
Learn SQL
SQL Course

Check also the database tutorials here too.

This post has been edited by JITHU: 11 June 2010 - 04:44 AM

Was This Post Helpful? 2
  • +
  • -

#8 RMckelvie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 16-April 10

Re: How do I create multiple LINQ Queries

Posted 11 June 2010 - 08:04 AM

That was handy, thanks. Will probably land into other problems, will keep you all updated.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1