This project has moved and is read-only. For the latest updates, please go here.

Lookup query by id

Mar 2, 2010 at 2:47 PM

hello.

When quering by id for lookup field you should be able to add LookupId="true" to the FieldRef attribute.

Please add it to FieldRef element if a field is a lookup by default. Or show us a way to add custom attribute to FieldRef element (preferred way).

Thanks

Mar 2, 2010 at 5:57 PM

hello mihailsamson

currently Camlex doesn't add LookupId="true" to the FieldRef attribute for lookup ids. Camlex allows you to customize resulting Caml by using linq 2 xml:

 

// get camlex query
var caml = Camlex.Query().Where(x => x["Fruit"] == (DataTypes.Lookup)"15");

// get XElement[]
var xml = caml.ToCaml(false)[0];

// add LookupId attribute to the FieldRef element using linq 2 xml
var fieldRef = xml.Descendants("FieldRef").Select(c => c).ElementAt(0);
fieldRef.Add(new XAttribute("LookupId", "True"));

Console.WriteLine(xml.ToString());

It will produce the desired caml:

<Where>
  <Eq>
    <FieldRef Name="Fruit" LookupId="True" />
    <Value Type="Lookup">15</Value>
  </Eq>
</Where>

 

Mar 2, 2010 at 6:05 PM

It is under question how to implement this feature in Camlex automatically. Suppose that we have the following expression:

var caml = Camlex.Query().Where(x => x["Fruit"] == (DataTypes.Lookup)"15");

How Camlex can understand that "15" in this case if lookup id instead of lookup value? We should support both these cases as CAML supports both lookup values and ids. Also we should keep in mind that lookup field may have integer value i.e. 15;#15 (i.e. 15 here is both id and value). So we should have possibility to explicitly specify that this is id not value. It can be done for example with the following extension method for the System.String:

var caml = Camlex.Query().Where(x => x["Fruit"] == (DataTypes.Lookup)"15".LookupId());

But it is not very intuitive. Do you have any better ideas?

Mar 3, 2010 at 7:01 AM

I think that you should search for ID by default:

var caml = Camlex.Query().Where(x => x["Fruit"] == (DataTypes.Lookup)"15");
And in most(!) cases it'll be OK. Often you have a datagrid which can sort and filter values based on titles. 
Taking in mind that joining lists could not be made with caml :o(, you will have problems quering by title in all cases.
Moreover, you already now the type of value i am comparing with ("15" is string and 15 is number). So you can add suitable attributes too :o)
Mar 3, 2010 at 7:04 AM

Your tool is better than CAML.net only for an ability of including more than 2 and/or in one clause, but that is a very helpful one. Please use information about type of values which i'm comparing to and the tool will be of a real help!

Mar 3, 2010 at 7:47 AM

As caml supports quering by titles, then we should suppot it too. From our real life experience it is ~ 50/50 people who use ids or titles in queries. But anyway you suggestion is quite reasonable. Reading your post I thought about possible solution:

 

var caml = Camlex.Query().Where(x => x["Fruit"] == (DataTypes.Lookup)"15");

 

should produce caml without LookupId="True" attribute as string "15" is specified. But the following caml

 

var caml = Camlex.Query().Where(x => x["Fruit"] == (DataTypes.Lookup)15);

should produce caml with  LookupId="True" as integer value 15 is used. One problem is that such syntax (with non-C# types in rvalue) we called string-based syntax. But with solution mentioned above it will be non-truly "string-based" :) But I think that we can live with it if it will make Camlex.NET more convenient.

Thanks you for your feedback. We work over next release now and I think that it is possible to include this feature to it

Mar 3, 2010 at 11:19 AM

Thank you for your answer

Mar 3, 2010 at 12:05 PM

Probably, the simplest way is to split DateTypes.Lookup class into two classes - DateTypes.LookupId and DateTypes.LookupValue. Usage of proper class gives needed information.

The examples of usage:

var caml = Camlex.Query().Where(x => x["Fruit"] == (DataTypes.LookupId)15); // it will mean that search should be done by ID

var caml = Camlex.Query().Where(x => x["Fruit"] == (DataTypes.LookupValue)"15"); // it will mean that search should be done by value

What do you think about it?

Mar 8, 2010 at 11:03 PM
Edited Mar 8, 2010 at 11:07 PM

This last is the best approach.  Attempting to intuit whether the lookup is by ID vs by title is doomed to be wrong at least sometimes.

A similar issue will arise when dealing with datetimes.  There are optional CAML attributes for dateonly versus date+time and local versus UTC.  You won't be able to guess correctly which one the developer means.

Overall comment: While your example above of using LINQ to XML to clean up the generated CAML is logically correct, using it means the developer needs to understand CAML, and understand the specific generated CAML in this case well enough to write LINQ code to pluck out the correct fieldref element, and then correctly insert the CAML amendment.

By the time he/she has done that, it would have been less effort to do what we've all traditionally done: make up a big string & use String.Format(MyCAMLTemplate, param1, param2, param3,...); to populate it at runtime.

In other words, this isn't really a practical solution to working around gaps in CAMLex. It's 100% OK to have gaps & unsupported cases.  I'd just suggest that offering LINQ to CAML as a fixup is probably worse than just saying "Sorry, we don't support that case (yet)."

Overall, this is an awesome tool & I appreciate the thinking & effort going into it.