This project is read-only.

Querys with JOIN

May 31, 2009 at 6:50 PM

Hello!

I have "LINQ To SharePoint v0.2.4.0" with VS 2008 and WSS 3.0.

I can't use function JOIN in my queries, why?

Example:

private System.Net.NetworkCredential CredenciaisU = new System.Net.NetworkCredential("hcdadmin", "#cd@dm!n09");

public DataSet GetSituacaoCadastral(String VRegional, String VDistri, String VFunc) {

OSharePointDataContext dsSC = new OSharePointDataContext();

dsSC.Credentials = CredenciaisU;

DataSet ds = new DataSet();

var OUsuarios = dsSC.Usuarios;

var ODistribuidores = dsSC.Distribuidores;

var querySC = from u in OUsers //from d in ODistribuidores

//where u.Cargo == Funcao && u.Distribuidor == Distribuidor// && u.Distribuidor == d.Distribuidor

join d in ODistri on u.Distri equals d.Distri

where u.Cargo == VFunc && d.Distri == VDistri && d.Regional == VRegional

select new {Name = u.FullName, Distri = u.Distri};

DataTable dtSC = new DataTable("SituationC");

//

dtSC.Columns.Add("FullName");

dtSC.Columns.Add("Distri");

//

DataRow row;

foreach (var nSC in querySC) {

//

row = dtSC.NewRow();

row["FullName"] = nSC.Name;

row["Distri"] = nSC.Distri;

dtSC.Rows.Add();

}

ds.Tables.Add(dtSC);

return ds;

}

Thanks!!!

Wagner Amorim
wa.amorim@hotmail.com

Jun 1, 2009 at 10:36 AM

Hi Wagner,

LINQ to SharePoint doesn't support joins, as the underyling query language (CAML) hasn't built-in support for it. Implementing joins would require the provider to have to download big chunks of data to the client in order to execute the join code there. In general that's not a good thing as the remote execution properties of the LINQ query become superfluous. Rather, to make developers aware of this, the provider doesn't try to be smart and requires local joins to be executed explicitly using LINQ to Object. Recall that you can turn execution from remote into local by calling the AsEnumerable() method. For example, you could have two queries to select the sources to be joined, each of which you call AsEnumerable on, and then use with LINQ join syntax to have the join execute locally:

from x in (from y in src.Z where ... select y).AsEnumerable()
join a in (from b in src.C where ... select b).AsEnumerable() on x.K equals a.L
...
select ...

Hope this helps,
-Bart