CAML query with Beginwith/EQ/Contains/ Retrieving All rows from Lists

Question

I have a caml query to retrieve only specific row which matched to the criteria, but it returns all the items in the LIST,

@"<Where><BeginsWith><FieldRef Name='custaname' /><Value Type='Lookup'>Prab</Value></BeginsWith></Where>";

with c# code it is like below

string getAllquery = @"<Where><BeginsWith><FieldRef Name='custaname' /><Value Type='Lookup'>Prab</Value></BeginsWith></Where>";
            ServerProperties.custom.ServerProperties SP = new ServerProperties.custom.ServerProperties();
            SPWeb _SPWeb = SP.GetConnected();
            SPList _SPlist = _SPWeb.Lists["Orders"];
            SPListItemCollection PubCollection = _SPlist.GetItems(getAllquery);

This query working perfect with CAML builder.

what i have missed here.?

Answer 1

Adding Orderby element was helped to make it work as follows.

 @"<Where><BeginsWith><FieldRef Name='custaname' /><Value Type='Lookup'>Prab</Value></BeginsWith></Where><orderby><FieldRef Name='title' Ascending='false;/></orderby>";

Answer 2

Your code

"<Where><BeginsWith><FieldRef Name='custaname' /><Value Type='Lookup'>Prab</Value></BeginsWith></Where>"

Try add <View><Query>...</Query></View> in your code

"<View><Query><Where><BeginsWith><FieldRef Name='custaname' /><Value Type='Lookup'>Prab</Value></BeginsWith></Where></Query></View>"

Look this https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.list.getitems.aspx

Something like this

CamlQuery query = new CamlQuery();
        query.ViewXml = "<View><Query><Where><BeginsWith><FieldRef Name='custaname' /><Value Type='Lookup'>Prab</Value></BeginsWith></Where></Query></View>";
        ListItemCollection collListItem = targetList.GetItems(query);

EDIT

For query.ViewXml write

CamlQuery query = new CamlQuery();
        query.ViewXml = "<View><Query><Where>...</Where></Query></View>";

And for query.Query write without <View> and <Query>

CamlQuery query = new CamlQuery();
        query.Query = "<Where>...</Where>";