inserting join statement

[noobprogrammerr]

public List<EventEntity> GetCurrentUpComingEventsByEstate(string estName)
        {
            EventDBModelDataContext context = new EventDBModelDataContext();
            var res = from e in context.EventEntities
                      where e.estateName==estName
                      && e.eventDate >= DateTime.Now
                      select e;
            return res.ToList();
        }

[OperationContract]
        [WebInvoke(Method = "GET", UriTemplate = "GetCurrentUpComingEventsByEstate?estateName={estName}",
          RequestFormat = WebMessageFormat.Xml,
          ResponseFormat = WebMessageFormat.Xml)]
        List<EventEntity> GetCurrentUpComingEventsByEstate(string estName);

Hi guys, may i know how to add in a joint statement so that i'll be able to select data in multiple tables ?
I have 2 database table; EventEntity and Estate. the eventDate and estateName belongs to the EventEntity table whereas in my Estate table, there is only one field, estateName. Can someone help me with coding whereby i can join both table together and retrieve the data out.
Thanks.(:

[thines01]

Generically speaking, there are at least two methods of doing what you want.
Here is a neutral example of how it can be done. You should be able to see how to modify your code when you see these two examples. If you are still (really) having problems, let me know.

The example does a translation from Spanish to english using a common KEY (in the key/value pair).
It will return the English word for any of the three Spanish words using the actual number (as the key) in the join.

Also be sure to see join on MSDN for future reference.

using System;
using System.Collections.Generic;
using System.Linq;

namespace CS_TEST_2010_NOV_2011
{
   using KVP_S2S = KeyValuePair<string, string>;
   using LST_KVP_S2S = List<KeyValuePair<string, string>>;
   class Program
   {
      static void Main(string[] args)
      {
         LST_KVP_S2S lst_kvp_s2sEnglish = new LST_KVP_S2S()
         {
            new KVP_S2S("1", "one"),
            new KVP_S2S("2", "two"),
            new KVP_S2S("3", "three"),
         };

         LST_KVP_S2S lst_kvp_s2sSpanish = new LST_KVP_S2S()
         {
            new KVP_S2S("1", "uno"),
            new KVP_S2S("2", "dos"),
            new KVP_S2S("3", "tres"),
         };

         ///////////////////////////////////////////
         // Method 1 // using the double from-clause
         string strTranslation1 =
         (
            from kvpE in lst_kvp_s2sEnglish
            from kvpS in lst_kvp_s2sSpanish
            where kvpE.Key.Equals(kvpS.Key)
            && kvpS.Value.Equals("dos")
            select kvpE.Value
         ).First().ToString();

         ///////////////////////////////////////
         // Method 2 // using the "join" clause
         string strTranslation2 =
         (
            from kvpE in lst_kvp_s2sEnglish
            join kvpS in lst_kvp_s2sSpanish on kvpE.Key equals kvpS.Key
            where  kvpS.Value.Equals("dos")
            select kvpE.Value
         ).First().ToString();

         Console.WriteLine(strTranslation1);
         Console.WriteLine(strTranslation2);
      }
   }
}

[noobprogrammerr]

Hi, i don't really seem to understand still. Do you mind helping me based on the codes i gave you ? Will really appreciate your help. I need to get the events by the estate name and also join it together with the event date. Hope you understand my explaination. (:

[thines01]

I made the example very simple and you should study it so you understand what your code needs to do.

Without knowing the layout of your entities, I can only guess it would look something like one of these examples:

var res =
	from e in context.EventEntities
	from es in context.Estate
	where e.estateName.Equals(es.estateName)
	&& e.eventDate >= DateTime.Now
	select e;

// ...OR...

var res =
	from e in context.EventEntities
	join es in context.Estate on e.estateName equals es.estateName
	&& e.eventDate >= DateTime.Now
	select e;

[noobprogrammerr]

Thanks for ur help!

[thines01]

Sorry about that typo. That last && should be the word "where".