How to solve this Azure App Service error querying Azure SQL DB

Question

I have a Xamarin app that hits an Azure App Service that queries a SQL DB in Azure.

I'm using the Nuget Microsoft.Azure.Mobile.Client package in my app

I started with the standard Quick Start code and modified it to fit my app. I believe the problem is in the schema that I'm using to keep my tables separate from others in the DB, but I can't find any definitive info on specifying a schema in my code that queries the DB. I did change the connection string to use a login that has a user that defaults to the schema, but that didn't solve it. Here's the relevant code:

Data Object:

public class Game : EntityData  
{  
   public int ID { get; set; }  
   public string Name { get; set; }  
   public int Genre { get; set; }  
   public string URL { get; set; }  
}  

SQL Table:

App Code:

  public partial class RPGGamersHubService  
    {  
        public string ApplicationURL = @"http://rpggamershub.azurewebsites.net/";  
  
        static RPGGamersHubService defaultInstance = new RPGGamersHubService();  
        MobileServiceClient client;  
        IMobileServiceTable<Game> gamesTable;  
  
        private RPGGamersHubService()  
        {  
            this.client = new MobileServiceClient(ApplicationURL);  
        }  
  
        public static RPGGamersHubService DefaultManager  
        {  
            get  
            {  
                return defaultInstance;  
            }  
            private set  
            {  
                defaultInstance = value;  
            }  
        }  
  
        public MobileServiceClient CurrentClient  
        {  
            get { return client; }  
        }  
  
        public async Task<ObservableCollection<Game>> GetGamesAsync(bool syncItems = false)  
        {  
            try  
            {  
                gamesTable = client.GetTable<Game>();  
  
                IEnumerable<Game> items = await gamesTable  
                    .ToEnumerableAsync();  
  
                return new ObservableCollection<Game>(items);  
            }  
            catch (MobileServiceInvalidOperationException msioe)  
            {  
                Debug.WriteLine("Invalid sync operation: {0}", new[] { msioe.Message });  
            }  
            catch (Exception e)  
            {  
                Debug.WriteLine("Sync error: {0}", new[] { e.Message });  
            }  
            return null;  
        }  
    }  

Service Context:

public class RPGGamersHubServiceContext : DbContext  
{  
    // You can add custom code to this file. Changes will not be overwritten.  
    //   
    // If you want Entity Framework to alter your database  
    // automatically whenever you change your model schema, please use data migrations.  
    // For more information refer to the documentation:  
    // http://msdn.microsoft.com/en-us/data/jj591621.aspx  
  
    private const string connectionStringName = "Name=RPGGamersHubConnectionString";  
  
    public RPGGamersHubServiceContext() : base(connectionStringName)  
    {  
    }   
  
    public DbSet<Game> Games { get; set; }  
    public DbSet<User> Users { get; set; }  
    public DbSet<Event> Events { get; set; }  
    public DbSet<Module> Modules { get; set; }  
    public DbSet<Character> Characters { get; set; }  
    public DbSet<UserEvent> UserEvents { get; set; }  
    public DbSet<EventRating> EventRatings { get; set; }  
    public DbSet<User> Friends { get; set; }  
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)  
    {  
        modelBuilder.Conventions.Add(  
            new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(  
                "ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));  
    }  
}  

Service Controller:

public class GamesController : TableController<Game>  
{  
    protected override void Initialize(HttpControllerContext controllerContext)  
    {  
        base.Initialize(controllerContext);  
        RPGGamersHubServiceContext context = new RPGGamersHubServiceContext();  
        DomainManager = new EntityDomainManager<Game>(context, Request);  
    }  
  
    // GET tables/TodoItem  
    public IQueryable<Game> GetAllGames()  
    {  
        return Query();  
    }  
  
    // GET tables/TodoItem/48D68C86-6EA6-4C25-AA33-223FC9A27959  
    public SingleResult<Game> GetGame(string id)  
    {  
        return Lookup(id);  
    }  
  
    // PATCH tables/TodoItem/48D68C86-6EA6-4C25-AA33-223FC9A27959  
    public Task<Game> UpdateGame(string id, Delta<Game> patch)  
    {  
        return UpdateAsync(id, patch);  
    }  
  
    // POST tables/TodoItem  
    public async Task<IHttpActionResult> AddGame(Game item)  
    {  
        Game current = await InsertAsync(item);  
        return CreatedAtRoute("Tables", new { id = current.ID }, current);  
    }  
  
    // DELETE tables/TodoItem/48D68C86-6EA6-4C25-AA33-223FC9A27959  
    public Task DeleteGame(string id)  
    {  
        return DeleteAsync(id);  
    }  
}  

The View I'm showing:

[XamlCompilation(XamlCompilationOptions.Compile)]  
public partial class GamesListPage : ContentPage  
{  
    public ObservableCollection<Game> Games { get; set; }  
  
    public GamesListPage()  
    {  
        InitializeComponent();  
  
        Games = new GamesViewModel().Games;  
  
        MyListView.ItemsSource = Games;  
    }  
  
    async void Handle_ItemTapped(object sender, ItemTappedEventArgs e)  
    {  
        if (e.Item == null)  
            return;  
  
        await DisplayAlert("Item Tapped", "An item was tapped.", "OK");  
  
        //Deselect Item  
        ((ListView)sender).SelectedItem = null;  
    }  
}  
The ViewModel I'm using:  
  
   public class GamesViewModel : BaseViewModel  
    {  
        public ObservableCollection<Game> Games { get; set; }  
        public Command LoadGamesCommand { get; set; }  
  
        public GamesViewModel()  
        {  
            Title = "Games";  
            Games = new ObservableCollection<Game>();  
            LoadGamesCommand = new Command(async () => await ExecuteLoadGamesCommand());  
  
            LoadGamesCommand.Execute(null);  
        }  
  
        async Task ExecuteLoadGamesCommand()  
        {  
            if (IsBusy)  
                return;  
  
            IsBusy = true;  
  
            try  
            {  
                Games.Clear();  
                var games = await RPG_Gamers_Hub.Services.RPGGamersHubService.DefaultManager.GetGamesAsync();  
                foreach (var game in games)  
                {  
                    Games.Add(game);  
                }  
            }  
            catch (Exception ex)  
            {  
                Debug.WriteLine(ex);  
            }  
            finally  
            {  
                IsBusy = false;  
            }  
        }  
    }  

The error I'm getting is:

> Microsoft.WindowsAzure.MobileServices.MobileServiceInvalidOperationException: 'The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.'

occurs here:

IEnumerable<Game> items = await gamesTable  
    .ToEnumerableAsync();  

in the first chunk of code above in the GetGamesAsync method.

Any idea what am I missing?

Answer 1

HI @Jim Perry ,

I would try adding the Table attribute to your EntityData object

[Table("Game", Schema="RPGGamersHub")]  
public class Game : EntityData  
{  
	…  
}  

You can also add modelBuilder.HasDefaultSchema("RPGGamersHub") to your OnModelCreating override method. I would also check fiddler when you run your API service locally to see how the request looks with the schema tied to it.

Hope this helps.